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
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...
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 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...
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...
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...
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...
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 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 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))...
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...
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...
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 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.
Excel contains special functions that will let you extract the day, month, and year from a valid date. Let's take a look.
Related functions
