Excel YEAR Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 104 of 105 · Full Excel course

The Excel YEAR function returns the year of a date as a four-digit number from 1900 to 9999. It reads the date’s serial number, so it works on real dates, timestamps and text that Excel recognises as a date. YEAR is used to group data by year, build fiscal-year labels, calculate approximate ages and rebuild dates together with MONTH and DAY.

Table of Contents

Syntax

=YEAR(serial_number)

Arguments

Argument	Required / Optional	Meaning
serial_number	Required	The date to read: a date cell, TODAY(), a DATE result or recognisable date text such as “2026-09-04”.

How YEAR works

Excel stores dates as serial numbers counted from 1 January 1900. YEAR converts the number to a calendar date and returns the year part, ignoring any time. =YEAR("2026-09-04 18:30") returns 2026 and =YEAR(TODAY()) returns the current year. The result is a plain integer, so it can be compared and used in arithmetic. Text that is not a date returns #VALUE!, negative numbers return #NUM!, and a cell in date format will display 2026 as a date in the year 1905, so keep the result cell in General format.

Step-by-step example: yearly totals from a transaction list

Column A holds transaction dates and column B the amounts.
In C2 enter =YEAR(A2) and fill down. Each row now shows its year.
In E2:E5 list the years present, for example 2023 to 2026, and in F2 enter =SUMIF($C$2:$C$1000,E2,$B$2:$B$1000).
Fill F2 down. Column F shows the total for each year. Add =F3/F2-1 in G3 to show year-on-year growth.

Practical use cases

1. Fiscal year that starts in April

="FY"&YEAR(A2)+(MONTH(A2)>=4)&"-"&RIGHT(YEAR(A2)+(MONTH(A2)>=4)+1,2)

4 September 2026 returns FY2026-27; 15 February 2027 also returns FY2026-27.

2. Exact age (not just a year difference)

=YEAR(TODAY())-YEAR(A2)-(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY())

Subtracting the years alone overstates the age before the birthday; the extra term corrects it. DATEDIF(A2,TODAY(),”y”) is the shorter equivalent.

3. Same date last year

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))

4. Leap-year test

=DAY(DATE(YEAR(A2),2,29))=29

If the year is not a leap year, DATE rolls 29 February into 1 March and DAY returns 1.

5. Count records in the current year

=SUMPRODUCT(--(YEAR($A$2:$A$1000)=YEAR(TODAY())))

Common mistakes and tips

Age by subtraction: YEAR(TODAY())-YEAR(birth) is wrong until the birthday has passed. Use DATEDIF or the corrected formula above.
Result formatted as a date: switch the cell to General.
Dates stored as text: a left-aligned column may need DATEVALUE or Text to Columns first; YEAR does accept recognisable text, but regional formats can be misread.
Two-digit years in text: “04/09/26” is read as 2026, while 30 to 99 map to 1930 to 1999.
Years before 1900: Excel dates start at 1900; earlier years cannot be stored as dates and need text or numeric handling.

YEAR compared with TEXT, DATEDIF and YEARFRAC

YEAR returns a number that is ideal for grouping, sorting and SUMIF criteria. TEXT(A2,”yyyy”) returns the same four digits as text, which suits labels but not arithmetic, and TEXT(A2,”yy”) gives the two-digit form. DATEDIF with the “y” unit measures complete years between two dates and is the correct tool for ages and tenure, whereas YEAR only reads a single date. YEARFRAC returns the interval between two dates as a decimal number of years for interest and pro-rata calculations. In PivotTables, grouping a date field by years achieves the same summary as a YEAR helper column without formulas; the helper column remains useful for SUMIFS models, charts and data validation where the year must exist as a cell value.