YEAR function
Quick answer
YEAR returns the four-digit year from a date.
- Syntax
=YEAR(serial_number)- Most common use
=YEAR(B2)returns 2025 (The year of the first invoice)
It is the quickest way to group transactions by year, check which year a date falls in, or build a year column for a pivot table or SUMIFS report.
- Available in
- Every current version
- Category
- Date functions
- Returns
- A whole number such as 2026.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=YEAR(serial_number)
| Argument | Required? | What it is |
|---|---|---|
serial_number | Required | The date to read, usually a cell reference. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =YEAR(B2) | 2025 | The year of the first invoice |
| E3 | =YEAR(B3)-YEAR(B2) | 1 | The calendar year changed, even though the invoices are a week apart |
| E4 | =SUMPRODUCT((YEAR(B2:B4)=2026)*C2:C4) | $3,000 | Total of the invoices dated in 2026 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Invoice | Date | Amount | Result | |
| INV-101 | 12/29/2025 | $1,200 | 2025 | |
| INV-102 | 1/5/2026 | $860 | 1 | |
| INV-103 | 2/14/2026 | $2,140 | $3,000 |
Practice YEAR
From lesson 23, DATE, YEAR, MONTH & TEXT. Graded instantly, then re-checked on changed data.
Year and month
B2:B6 with the year and C2:C6 with the month number of each invoice date.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A date like 7/18/1905 instead of 2026 | The cell inherited a date format. YEAR returns a plain number; format the cell as General or Number. |
| #VALUE! | The date is text that Excel can't read. Convert it with DATEVALUE first, or fix the import so the column holds real dates. |
| Ages that are off by one | Subtracting years ignores whether the birthday has passed. Use DATEDIF(start, end, "Y") for completed years. |
Tips
- Use YEAR in a helper column so SUMIFS can total by year: =SUMIFS(C:C, D:D, 2026).
Questions
How do I get the year from a date in Excel?
Use =YEAR(A2). It returns a number like 2026 that you can compare, sum by or use in other formulas.
How do I calculate age from a birth date?
YEAR(TODAY())-YEAR(A2) overcounts before the birthday. Use =DATEDIF(A2,TODAY(),"Y") for completed years.
Lessons that use YEAR
Answers that use YEAR
In other languages
YEAR is AÑO in Spanish Excel and JAHR in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .