Function guides ยท Date
YEAR function
Returns the year of a date as a four-digit number.
Syntax
=YEAR(serial_number)
| Argument | What it is |
|---|---|
serial_number | A date, as a cell reference, a DATE result, or a date typed in quotes. |
What YEAR does
YEAR returns the year of a date as a four-digit number. It is one of a trio with MONTH and DAY that take a date apart.
Splitting out the year lets you group, filter and total by year. A helper column of YEAR next to the dates makes it simple to count the orders in 2025 or compare 2025 with 2026.
The input must be a real date or text that Excel can read as one. A number that is not a date is read as a serial number, so YEAR(5) is 1900.
For exact tenure and age, the whole-year subtraction is only an approximation. DATEDIF does it properly.
YEAR also helps in comparisons across years. A column of YEAR next to a column of amounts supports a quick SUMIFS by year and a year-over-year difference beside it. When the data covers a single year, the helper is unnecessary. When it covers several, forgetting it is how totals end up mixing 2025 and 2026.
The current year is YEAR(TODAY()). Using that in a formula keeps age, tenure and annual reports current without editing.
YEAR examples
1. The year of an order
| A | B | C | |
|---|---|---|---|
| 1 | Order | Date | |
| 2 | 1001 | 3/14/2026 | 2026 |
YEAR pulls the year out of a date. Orders can then be grouped, filtered or counted by it. The order date here is March 14, 2026.
2. Years of service, roughly
| A | B | C | |
|---|---|---|---|
| 1 | Name | Hired | |
| 2 | Kai | 6/1/2020 | 6 |
Subtracting years gives a quick tenure figure. It ignores the month, so someone hired in December 2020 shows 6 years on January 5, 2026. DATEDIF is the exact method.
3. Sum sales for one year
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | ||
| 2 | 12/20/2025 | 300 | 1070 | |
| 3 | 1/15/2026 | 450 | ||
| 4 | 3/14/2026 | 620 |
SUMIFS cannot apply YEAR to its criteria range, but SUMPRODUCT can. This adds the amounts where the date falls in 2026, skipping the 2025 row.
Common YEAR mistakes
- Text dates. A date stored as text may fail or work depending on locale. If YEAR returns #VALUE!, convert with DATEVALUE.
- Counting years by subtraction. 2026 minus YEAR(hire date) overstates tenure by up to a year. Use DATEDIF for completed years.
- Using YEAR on a number that is not a date. YEAR(5) is 1900, since 5 is a serial number. Check the cell holds a date.
- Wrapping it in SUMIFS criteria. SUMIFS needs a range, not a calculation. Use a date window with >= and <, or SUMPRODUCT.
- Two-digit years from imports. A year of 26 is read as 1926 in some imports. Check the source.
YEAR on Excel job tests
Prompts: "add a column showing the year of each order", "total sales by year", "count orders in 2025". For totals by year there are three workable answers: a pivot table grouped by years, SUMIFS with a window from January 1 to the next January 1, and SUMPRODUCT with YEAR. The date-window SUMIFS is quickest on big ranges.
If the date column has text entries, those rows show #VALUE!. Decide whether to clean them or wrap the formula in IFERROR, and label any helper column so a reader knows what it is.
Related functions
YEAR questions
- How do I get the year from a date?
=YEAR(A2)returns it as a number.- How do I sum by year?
- Use SUMIFS with a date window, or a pivot table grouped by years.
- What does YEAR return for text?
- If Excel can read the text as a date, the year. If not, #VALUE!.
- How do I get the current year?
=YEAR(TODAY()).
Practice YEAR
Try it in a working grid: the free lesson DATE, YEAR, MONTH and DAY (module 10 of Everyday Excel) has checked YEAR exercises.
Practice this function