YEAR function
YEAR returns the four-digit year from a date. 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 17, 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
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.