YEAR Function

The YEAR function returns the year of a date as a 4-digit number. YEAR takes just one argument, date, which must be a valid date. Date can be a cell containing a date, a date string, or a formula that returns a date. Internally, Excel converts dates to serial numbers following its own date system. For example, given the date "23-Aug-2012", YEAR will return 2021:

=YEAR("23-Aug-2012") // returns 2012

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported.

Key features

• Returns year as 4-digit number (e.g., 2024, not 24)
• Works with any valid Excel date
• Often combined with DATE, MONTH, DAY for date manipulation
• Returns a number, not a text value
• Can accept dates as text strings, cell references, or formulas

Table of contents

• Basic examples
• Get year from date
• Add years to date
• Get fiscal year from date
• Count dates in given year
• Sum by year
• Year is a leap year
• Notes

Basic examples

The YEAR function requires just one argument, which must be a valid date or a value that Excel can convert into a valid date. With the date January 20, 2026, in cell A1, the following formula will return 2026:

=YEAR(A1) // returns 2026

Note that you can use YEAR to extract the year from a date entered as text:

=YEAR("1/20/2026") // returns 2026

However, using text for dates can cause unpredictable results on computers using different regional date settings. In general, it's better to supply a reference to a cell that already contains a valid date. 

You can easily combine the YEAR function with other Excel functions. For example, to get the current year, you can use YEAR with the TODAY function like this:

=YEAR(TODAY()) // returns current year

Using the DATE function, you can extract a year from cell A1 and use it to create the date January 1 in the same year, like this:

=DATE(YEAR(A1),1,1) // first day of same year

If A1 contains January 20, 2026, the result will be January 1, 2026.

Get year from date

Use the DATE function to extract just the year from a date. In the worksheet below, the formula in D5 is:

=YEAR(B5)

The YEAR function takes just one argument, the date from which you want to extract the year. With a date value for 15-Apr-1912 in B5, the YEAR function returns the number 1912. For more details, see Get year from date.

Add years to date

In this example, the goal is to add a given number of years to a date. The formula in E5 is:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

This formula uses the DATE function together with the YEAR, MONTH, and DAY functions. Working from the inside out, YEAR, MONTH, and DAY "take apart" the date into separate components. The DATE function then reassembles the date, adding the number in C5 to the year value along the way. With the date 8-Mar-1960 in B5, and the number 10 in C5, the result is 8-Mar-1970.

Another approach is to use the EDATE function. For more details, see Add years to date.

Get fiscal year from date

The YEAR function only returns a normal year number, not a fiscal year. However, you can get YEAR to return a fiscal year by combining it with the MONTH function. You can see this approach below, where the fiscal year starts in July. The formula in D5 is:

=YEAR(B5)+(MONTH(B5)>=7)

By convention, a fiscal year is denoted by the year in which it ends. So if a fiscal year begins in July, then July 1, 2026 is in fiscal year 2027, while June 1, 2026 is in fiscal year 2026. The YEAR function first extracts the year from the date. Then, a boolean expression is added to adjust for the fiscal year:

+(MONTH(B5)>=7) // returns 0 or 1

If the month is greater than or equal to 7 (July), the expression returns TRUE, which Excel evaluates as 1. This increments the year by one. If the month is less than 7, the expression returns FALSE (zero), and the year remains unchanged. 

For more details, see Get fiscal year from date.

Count dates in given year

In this example, the goal is to count how many dates fall in a given year. The formula in E5 is:

=SUMPRODUCT(--(YEAR(dates)=D5))

The YEAR function extracts the year from each date in the named range dates (B5:B16). Because B5:B16 contains 12 cells, YEAR returns an array of 12 year values. This array is compared to the year in D5, creating a new array of TRUE and FALSE values.

The double negative (--) converts the TRUE/FALSE values to 1s and 0s. Finally, SUMPRODUCT sums the array, returning a count of dates that match the year.

This pattern of using Boolean logic in array operations is powerful and flexible, and is also used with functions like FILTER and XLOOKUP.

For more details, see Count dates in given year.

Sum by year

In this example, the goal is to calculate a total for each year. All data is in an Excel Table named "data". The formula in G5 is:

=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

Working from the inside out, the YEAR function extracts year values from the dates in the data table. These year values are compared to the year in F5, creating an array of TRUE and FALSE values. This array is multiplied by the amounts, which converts TRUE/FALSE to 1/0 and effectively "cancels out" amounts from other years. SUMPRODUCT then sums the resulting array to return the total for the year.

For more details, see Sum by year.

Year is a leap year

Excel doesn't have a function to test for a leap year. However, you can roll your own by combining several functions together. In the worksheet below, the goal is to test whether the year of a date is a leap year for all dates in column B. The formula in D5 is:

=MONTH(DATE(YEAR(B5),2,29))=2

This formula exploits a behavior of the DATE function. When you request February 29 in a non-leap year, DATE automatically rolls forward to March 1. The YEAR function extracts the year from the date, and DATE constructs a date for February 29 of that year. Then the MONTH function is used to test the month number of the resulting date. If the month is 2 (February), it's a leap year (TRUE). If the month is 3 (March), it's not a leap year (FALSE).

Note: Excel incorrectly treats 1900 as a leap year due to a legacy bug from Lotus 1-2-3. To guard against this, you can add an AND condition: =AND(MONTH(DATE(YEAR(B5),2,29))=2,YEAR(B5)<>1900)

For more details, see Year is a leap year.

Notes

• YEAR returns a #VALUE! error if the date argument is not a valid date.
• Dates before January 1, 1900 are not supported in Excel's date system.
• The result is a number, not text. To display just the year from a date, you can apply a custom number format like "yyyy".
• YEAR can accept dates entered as text strings (e.g., "1/5/2016"), but this can cause issues with different regional date settings.
• Excel incorrectly treats 1900 as a leap year due to a legacy bug inherited from Lotus 1-2-3.