Excel YEAR Function
Summary
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.
Purpose
Get the year from a date
Return value
Year as 4-digit number
Syntax
=YEAR (date)
Arguments
- date - A valid Excel date.
Version
Usage notes
The YEAR function extracts the year from a given date as a 4-digit number. For example:
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:
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 a the number format of your choice.
Notes
- Date must be a valid Excel date.
- YEAR will return a #VALUE error on text values.
YEAR formula examples
the YEARFRAC function takes two dates, a start date and and an end date, and calculates the time between them in fractional years. In this case, we are using the end date as-is, but generating a start date that equals...
In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6)+1,MONTH(B6),DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...
The core of this formula is the DATE function, which will automatically adjust to month and year values that are out of range. In the formula, year is passed into date unchanged, along with 2 for month (February) and 29...
The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Using the lookup table...
By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019.
The year function first returns the year from the date in...
Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and...
This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:
=B5-DATE(...
This formula builds the final result in 2 parts, joined by concatenation with the ampersand (...
Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components:
=YEAR(B5) // 1960
=MONTH(B5) // 3
=DAY(B5) // 8
At the outer level, the DATE function simply reassembles the...
In the example, B6 is the hard-coded start date and the formula in B7 is:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then...
The YEAR function extracts the year from a valid date. In this case, we are giving YEAR and array of dates in the named range "dates", so we get back an array of results:
{2018;2017;2019;2019;2017;2019;2017;2019;2019...
Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.
When a user...
The YEAR function takes just one argument, the date from which you want to extract the year. In the example, the formula is:
=YEAR(B4)
B4 contains a date value for January 5, 2016. The YEAR function returns the...
The SEQUENCE function is a dynamic array function that can generate multiple results. Like other dynamic array functions, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....
In the example, the formula in cell D6 contains this formula:
=MONTH(B6)&YEAR(B6)=MONTH(C6)&YEAR(C6)
In this case, Excel extracts the month from the date in cell B6 as numbers, then concatenates them...
Related videos
Although you can use Excel's AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that you can easily change the starting value and generate a new series.
In this video we'll look at how to calculate the number of years or months between dates using a function called DATEDIF and a function called YEARFRAC. The DATEDIF function is a "compatibility" function that originated from Lotus 1-2-3.
Excel contains special functions that will let you extract the day, month, and year from a valid date.
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
