YEAR function
YEAR extracts the year from a date and returns it as a four-digit number. It is useful for grouping or filtering by year. A date stored as text depends on regional settings.
Example
Read it as a sentence: find the year of the date in F1, which is blank, so it gives 1900.
Excel shows 1900
F
1
Data
Syntax
YEAR(serial_number)
-
serial_number(required) - A date or date serial number.
Returns: The year as a four-digit number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=YEAR(46031) | 2026 | Serial |
=YEAR("2026-01-09") | 2026 | ISO text |
=YEAR(0) | 1900 | Serial 0 |
=YEAR(2958465) | 9999 | Last date |
=YEAR(Data!F1) | 1900 | Blank |
=YEAR(Data!F3:F3) | 2024 | Single-cell range |
=YEAR(-1) | #NUM! | Negative |
=YEAR("abc") | #VALUE! | Bad text |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
Data
Watch out for
- A text date depends on regional settings to be understood.
Where it works
- Excel
- YEAR is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .