The Excel YEAR function pulls the four-digit year out of any date — the first step in annual summaries, age calculations, and anniversary logic. Like its siblings MONTH and DAY, it reads one component from Excel’s underlying date serial number and hands you a plain number you can subtract, compare, and group on.
2026. Subtracting two YEARs gives a rough year count — for exact ages, see DATEDIF below.
Syntax
| Argument | Description | |
|---|---|---|
serial_number | Required | The date to read the year from — a cell with a real date, a DATE formula, or a raw serial number. The answer is always between 1900 and 9999. |
Dates are serial numbers. Excel counts days from January 1, 1900 (serial 1); June 15, 2026 is serial 46188, and times ride along as fractions of a day. YEAR reads the year out of that count — which is why it works on raw numbers and timestamps alike.
Extract the year from a date
Point YEAR at a date column to bucket records by year — here, hire dates with a service-length estimate (* assumes today is in 2026):
| A | B | C | |
|---|---|---|---|
| 1 | Hire date | =YEAR(A) | Years of service* |
| 2 | 03/12/2018 | 2018 | 8 |
| 3 | 06/15/2021 | 2021 | 4 |
| 4 | 11/30/2024 | 2024 | 1 |
| 5 | 01/05/2026 | 2026 | 0 |
The subtraction counts calendar-year boundaries crossed, not full years elapsed — someone hired in December 2024 shows "2" in January 2026 even though only 13 months passed. When that distinction matters, use DATEDIF with the "y" unit.
Try it: interactive YEAR demo
Pick a date and watch YEAR extract the year — with MONTH and DAY along for comparison, plus the serial number underneath it all.
Ages, anniversaries, and fiscal years
YEAR teams up with DATE to rebuild a date pinned to a different year — the anniversary pattern:
For exact ages and tenures, hand the problem to DATEDIF, which counts complete years:
Fiscal years that start mid-year are a one-liner: for a July–June fiscal year, any date from July onward belongs to the next FY:
Errors & common pitfalls
#VALUE! — the argument isn’t a recognizable date. Imported text like "2026.06.15" fails in a US locale. Convert with DATEVALUE or rebuild the date with DATE and text functions.
Pitfall: YEAR subtraction is not an age. =YEAR(TODAY())-YEAR(B2) says a person born December 31, 2000 is 26 on January 1, 2026 — off by a year until their birthday. Use =DATEDIF(B2, TODAY(), "y") for true completed years.
Pitfall: the result shows as 05/18/1905. That’s the number 2026 rendered through a date format (serial 2026 = May 18, 1905). The value is correct — set the cell’s format to General.
Pitfall: two-digit years in source data. Text dates like "6/15/26" are interpreted by Excel’s century cutoff rules (00–29 → 2000s, 30–99 → 1900s) when converted. Verify imported birthdates from the 1920s didn’t land in the 2020s.
Practice workbook
Frequently asked questions
How do I calculate age from a birthdate?
=DATEDIF(B2, TODAY(), "y") returns completed years and only ticks over on the birthday. =YEAR(TODAY())-YEAR(B2) overstates the age before the birthday each year.How do I get this year's anniversary of a past date?
Why does YEAR return #VALUE!?
How do I compute a fiscal year from a date?
Does YEAR work on timestamps with times?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class