| Function category | Date and time functions |
| Volatility | Non-volatile |
| Similar functions | MONTH — returns month number DAY — returns day number WEEKDAY — returns day of week number from date |
What does the YEAR function do?
The YEAR function extracts a four-digit year number from a given date. For example, from date 03/15/2025 it will return value 2025.
Examples:
- 01/01/2024 → 2024
- 12/31/1999 → 1999
The function is useful when you need to analyze data distribution by years, build pivot tables, group events or sales by calendar year.
YEAR: result and input format
The result is an integer. If necessary, you can change the cell's number format, but usually this is not required.
If you need to display the year as text, you can use the TEXT function:
=TEXT(A2,"yyyy")
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Amount | Year | Year | Total | |
| 2 | 01/15/2025 | 1200 | 2025=YEAR(A2) | 2025 | 2000=SUMIFS(B2:B5,C2:C5,E2) | |
| 3 | 06/03/2025 | 800 | 2025=YEAR(A3) | 2026 | 2200=SUMIFS(B2:B5,C2:C5,E3) | |
| 4 | 02/10/2026 | 1500 | 2026=YEAR(A4) | |||
| 5 | 09/28/2026 | 700 | 2026=YEAR(A5) |
A helper column with YEAR lets SUMIFS add up amounts by calendar year. Without a helper column: =SUMPRODUCT((YEAR(A2:A5)=2025)*B2:B5).
YEAR function syntax
=YEAR(date)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | YEAR | TEXT "yyyy" | TEXT "yy" |
| 2 | 03/15/2025 | 2025=YEAR(A2) | 2025=TEXT(A2,"yyyy") | 25=TEXT(A2,"yy") |
| 3 | 12/31/1999 | 1999=YEAR(A3) | 1999=TEXT(A3,"yyyy") | 99=TEXT(A3,"yy") |
| 4 | 1900=YEAR(A4) | 1900=TEXT(A4,"yyyy") | 00=TEXT(A4,"yy") | |
| 5 | not a date | #VALUE!=YEAR(A5) | not a date=TEXT(A5,"yyyy") | not a date=TEXT(A5,"yy") |
YEAR returns a number (right-aligned), TEXT returns text. An empty cell gives 1900 — check for blanks with IF(A4="","",YEAR(A4)). TEXT doesn’t fail on non-dates: it returns the text unchanged.
- date — required argument. Can be:
YEAR formula examples
| Date | Formula | Result |
|---|---|---|
| 03/15/2025 | =YEAR(A2) | 2025 |
| =TODAY() | =YEAR(TODAY()) | (current year) |
| – | =YEAR("1999-12-31") | 1999 |
YEAR features and limitations
- The function returns only the year number, without month and day.
- If the cell contains text that Excel cannot recognize as a date, the result is #VALUE!. An empty cell is not an error: it counts as day 0, so YEAR returns 1900.
- Excel considers dates starting from January 1, 1900.
Other Date and Time functions in Excel
TIMEVALUE, TIME, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, YEARFRAC, EOMONTH, MONTH, MINUTE, WEEKNUM and ISOWEEKNUM, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.