The Excel YEAR function, explained interactively.
YEAR extracts the year component from a date, returning an integer between 1900 and 9999. It’s the counterpart to DATE — where DATE assembles a date from year, month, and day, YEAR pulls the year back out. Pair it with MONTH and DAY to decompose any date into its three integer parts.
How to use YEAR
- Type
=YEAR(and supply a date — a cell reference containing a date, aDATE()formula, or any formula that returns a real Excel date serial. - Close the parenthesis. YEAR returns an integer — there are no extra arguments or options.
- The result is a plain integer, not a date. You don’t need to format the output cell as a date; it already shows the four-digit year directly.
- To use YEAR in grouping or counting, combine it with
SUMPRODUCT,SUMIFS, or a helper column. YEAR does not work directly insideCOUNTIF’s criteria string.
DATE() formula, or any formula returning a serial. Text that looks like a date (e.g. "2026-04-19") returns #VALUE! unless wrapped in DATEVALUE().| A | B | |
|---|---|---|
| 1 | Date | Serial |
| 2 | 2026-04-19 | 46131 |
| 3 | ||
| 4 | YEAR result | 2026 |
=YEAR(DATE(2026, 4, 19)) → 2026. That’s April 19, 2026 (Sunday) — result range 1900–9999.YEAR syntax and arguments
One required argument — the date to extract from. See Microsoft’s official YEAR reference for the canonical specification.
DATE() result, or any formula returning a date serial number. Text strings that look like dates (e.g. "2026-04-19") return #VALUE!; wrap with DATEVALUE to coerce text first. The valid range is 1900-01-01 through 9999-12-31 in Excel.YEAR examples
Four patterns covering the most common real-world YEAR use cases.
Example 1: Extract year from a column of dates
The most direct use — extract the year from each date in a sales table to enable fiscal year grouping.
Drag the formula down the column. Each row produces the four-digit year. Add a SUMIFS or pivot table on this helper column to aggregate by year.
Example 2: Approximate age in years
Subtract the birth year from the current year for a quick age estimate. Note: this is approximate — it ignores whether the birthday has occurred yet.
Fast and readable, but off by 1 before the birthday each year. For exact age use DATEDIF(B2, TODAY(), "Y") instead. See the error card below for the off-by-one demonstration.
Example 3: First day of the same month
Reconstruct the first of the month from any date by combining YEAR, MONTH, and DATE. Useful for creating period anchors in financial models.
Returns the 1st of the same month and year as A2, regardless of which day A2 falls on. Change A2 to any date in a different month and the result jumps to that month’s first day.
Example 4: Count rows where year = 2025
Use SUMPRODUCT with YEAR to count dates in a specific year — COUNTIF can’t do this directly.
YEAR(A2:A8) returns an array of year integers; comparing each to D2 produces TRUE/FALSE; multiplying by 1 converts to 1/0; SUMPRODUCT sums the 1s. Change D2 to any year to recount. For a sum instead, replace the outer *1 with *B2:B8.
Common YEAR errors and fixes
Three failure modes and one behavioural trap — each with what to check and how to recover.
YEAR returns #VALUE!
Cause: the argument is text, not a real date. Text strings like "2026-04-19" look like dates but are not date serials — YEAR can’t read them.
Wrap with DATEVALUE(): =YEAR(DATEVALUE(A2)). Or replace the text with a real DATE() formula. Check cell alignment — text dates left-align, real dates right-align.
YEAR returns a serial number instead of a year
Cause: the output cell is formatted as Date. YEAR returns a plain integer like 2026, but if the cell has a date format, Excel may display it as 1905-07-05 (the date with serial 2026).
Format the result cell as General or Number. YEAR’s output is an integer, not a date — it should never be formatted as a date.
YEAR age is off by 1 before the birthday
Cause: YEAR(TODAY())-YEAR(birth) counts calendar years, not elapsed years. Before the birthday arrives each year, the result is one too high.
For exact age: =DATEDIF(B2, TODAY(), "Y"). DATEDIF checks whether the anniversary has passed before incrementing the count. Reserve the YEAR approach for rough groupings where off-by-one within the year is acceptable.
YEAR inside COUNTIF criteria string
Cause: trying to write something like =COUNTIF(A:A, YEAR(A2)&"*"). COUNTIF criteria run against the raw cell values — it compares dates as serials, not extracted years.
Use SUMPRODUCT((YEAR(A2:A100)=2025)*1) or COUNTIFS with DATE-built boundary dates: =COUNTIFS(A2:A100, ">="&DATE(2025,1,1), A2:A100, "<="&DATE(2025,12,31)).
YEAR, MONTH & DAY compared
The three date-extraction functions are a family — they share the same single-argument syntax and always return integers.
| Function | Returns | Range | Typical use |
|---|---|---|---|
| YEAR | Year integer | 1900–9999 | Fiscal year grouping, age approximation, period anchors |
| MONTH | Month integer | 1–12 | Monthly grouping, CHOOSE for month names, seasonal flags |
| DAY | Day-of-month integer | 1–31 | Month-end checks, days-remaining, payroll cut-off flags |
| DATE | Date serial | Any valid date | Reconstruct a date from YEAR/MONTH/DAY components |
Rule of thumb: YEAR, MONTH, and DAY decompose a date into integers; DATE assembles integers back into a date. Use all four together when you need to manipulate individual date components — for example, =DATE(YEAR(A2), MONTH(A2), 1) for the first of the same month.
YEAR frequently asked questions
6.01What does the YEAR function return in Excel?▸
YEAR returns an integer in the range 1900–9999 representing the year of the date passed to it. It always returns a whole number — never a fraction or a text string. For example, YEAR(DATE(2026, 4, 19)) returns 2026.
6.02Why is YEAR(TODAY())-YEAR(birth_date) an approximate age?▸
Because it computes the difference in calendar years, not elapsed years. If today is April 19 and the birthday is June 15, the birthday hasn’t arrived yet this year — the person is still one year younger than the year difference suggests. For exact age in years, use DATEDIF(birth_date, TODAY(), "Y") instead.
6.03Does YEAR work on text that looks like a date?▸
No — YEAR requires a real Excel date serial number. Text strings like "2026-04-19" return #VALUE! even though they look like dates. Use DATEVALUE(A2) to convert a text date first: =YEAR(DATEVALUE(A2)). Alternatively, replace text dates with =DATE(year, month, day) formulas.
6.04What is the range of values YEAR can return?▸
In Excel, YEAR returns integers from 1900 through 9999 — the bounds of Excel’s date system. Google Sheets supports dates from 1899-12-30, so YEAR can return 1899 in Sheets. Values outside this range produce #NUM!.
6.05How do I count rows where the year matches a target year?▸
Use SUMPRODUCT with YEAR: =SUMPRODUCT((YEAR(A2:A100)=2025)*1). This counts every date in A2:A100 that falls in 2025. COUNTIFS with DATE-built boundary conditions is another approach: =COUNTIFS(A2:A100, ">="&DATE(2025,1,1), A2:A100, "<="&DATE(2025,12,31)).
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.