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.
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
If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the DAY, MONTH, YEAR, and DATE functions.
How the formula works...
If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function.
In the example, the formula in C5, which calculates percentage of year remaining...
To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.
How the formula works
In the example, B6...
To generate a series of dates by year, you can use the SEQUENCE function together with YEAR, MONTH, and DAY functions. In the example shown, the formula in E5 is:
=DATE(SEQUENCE(12,1,YEAR(B5)),MONTH(B5),DAY(B5))...
To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is:
=SUMPRODUCT(--(YEAR(dates)=D5))
where "dates" the a named range B5:B15.
How this formula...
If you need to test two dates to see they both have the same month and year, you can do so with a simple formula that uses the MONTH and YEAR functions.
How the formula works
In the example, the formula in cell D6...
If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions.
In the example shown, the formula in cell C5 is:
=MONTH(DATE(YEAR(B5),2,29...
Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility.
To allow a...
To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. In the example shown, the formula in C5 is:
=B4-DATE(YEAR(B4),1,0)
With the date "June 1, 2016" in cell B4...
To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions.
In the example shown, the formula in D5 is:
=DATE(YEAR(B5)+C5,MONTH(B5),...
If you need to extract the year from a date, you can use the YEAR function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date.
How the formula works
The YEAR...
If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions.
Background
"Julian date format" refers to a format where the year value...
To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. In the example shown, the formula in D5 is:
=YEAR(B5)+(MONTH(B5)>=C5)
How this formula works
By convention a fiscal...
Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility.
To allow a user to enter...
Related videos
Excel contains special functions that will let you extract the day, month, and year from a valid date. Let's take a look.
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 can easily change the starting value and generate a new series. Let's take a look.
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 "compatability" function that comes originally from Lotus 1-2-3.
Related functions
