Purpose
Return value
Syntax
=YEAR(date)- date - A valid Excel date.
How to use
The YEAR function extracts the year from a given date as a 4-digit number. For example:
=YEAR("23-Aug-2012") // returns 2012
=YEAR("11-May-2019") // returns 2019
You can use the YEAR function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function:
=DATE(YEAR(A1),1,1) // first of same year
See below for more examples of formulas that use the DATE function.
Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice.
Notes
- Date must be a valid Excel date.
- YEAR will return a #VALUE error on text values.
YEAR function examples
Sum by fiscal year
Add years to date
Get nth day of year
Data validation allow weekday only
Count birthdays by year
Year is a leap year
Sequence of years
Convert date to Julian format
Get fiscal year from date
Data validation date in specific year
Sum by year
Zodiac sign lookup
Count dates in given year
Date is same month and year
Get year from date
YEAR function videos
How to work with dates
How to create date and time series with formulas
How to calculate years and months between dates
Related functions
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.
MONTH Function
The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function.
DAY Function
The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the...
DATE Function
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
