Knowledge base / Excel
YEAR in Excel: year numbers, text dates and arrays
I distinguish a numerical year from a date display, then cover column extraction and pitfalls involving age, imports and calendar boundaries.

You need to group orders by year. The source contains full dates, so displaying four digits seems enough. I first decide whether I want to change the appearance of a date or actually obtain the numerical year. Those operations can look similar while producing different inputs for later calculations.
YEAR extracts the year from a date. I will build a helper column, explain text-date pitfalls and identify the function’s limits. A year number alone does not calculate age or determine whether the year is a leap year.
Syntax and a small example
=YEAR(A2)
The argument is a serial number representing an Excel date. YEAR returns the Gregorian year number. Microsoft recommends a recognized date or DATE rather than relying on date text, which can cause problems. See the YEAR reference.
These dates are synthetic. Put the header in A1 and dates in A2:A4, creating them with DATE as shown. In B2 enter =YEAR(A2) and fill down.
| Cell | Formula creating the date | Displayed date | YEAR |
|---|---|---|---|
| A2 | =DATE(2024,12,31) |
2024-12-31 | 2024 |
| A3 | =DATE(2025,1,1) |
2025-01-01 | 2025 |
| A4 | =DATE(2025,11,5) |
2025-11-05 | 2025 |
The final two records share a year despite different months and days. That is useful for grouping, but I retain the original date. It may still be needed for seasonality, event order or checking the report’s coverage.
I use English names and commas. The Polish function is ROK; argument separators depend on regional settings. A single-cell YEAR formula works in the releases listed by Microsoft, including Excel 2016-2024 and Microsoft 365.
A year number versus a date format
With a custom yyyy format, A2 displays 2024 but retains the full date December 31. B2 containing YEAR(A2) instead has the numerical result 2024. These are different underlying values despite the similar display.
For appearance alone, choose formatting. For a field used to join an annual plan or group records, create a separate year column. Retain the full source date beside it so the result remains explainable.
Format the YEAR output as General or Number. Applying a date format to the number 2024 treats it as a date serial, rather than creating a date in the year 2024. A year number is not a full date.
Format codes depend on language. English yyyy corresponds to Polish rrrr. Check the appropriate code in the Format Cells dialog; Microsoft explains using format codes with TEXT.

Text dates need an established convention
2025-11-05 may look like a date while remaining text imported from a CSV. Applying a date format does not guarantee conversion. Establish the source convention before extracting the year.
For date text recognized under the relevant regional settings, you can use:
=YEAR(DATEVALUE(A2))
DATEVALUE interprets supported text formats using the environment’s settings. A value such as 03/04/2025 is ambiguous without the source convention. Changing its display cannot establish whether March or April was intended.
ISNUMBER can help identify numerical values, but does not prove that a number represents the correct date. An arbitrary number also passes that test. I inspect imported date samples and pay particular attention to the transition from December 31 to January 1.
Excel supports the 1900 and 1904 date systems. A serial number can represent a different date depending on the workbook’s system. When transferring data between older files, consult the date-system rules instead of guessing serial numbers manually.
Extract years for several rows
The most portable method is =YEAR(A2) in B2, filled down. Each row has a separate result that can be compared with its source.
In an Excel version supporting dynamic arrays, such as Microsoft 365 or Excel 2021/2024, you can enter this outside an Excel table:
=YEAR(A2:A4)
The three results spill into neighboring cells when the output area is clear. Microsoft documents spilling behavior and table limitations. If the destination is blocked, clear the obstruction or choose another location.
Do not add ARRAYFORMULA. That is a Google Sheets function, not a required wrapper in Excel. In older Excel versions, filling down the single-cell formula avoids mixing syntax from two different applications.
What YEAR alone does not calculate
YEAR(TODAY())-YEAR(A2) subtracts year numbers. For a birth date of August 10, 1990 and a reference date of July 1, 2026, it produces 36 even though only 35 complete years have passed. The birthday has not arrived yet. See TODAY for complete years and reference dates.
Divisibility by 4 is also an incomplete leap-year rule for the Gregorian calendar. Century years must be divisible by 400: 2000 is a leap year, while 2100 is not. The rule is documented by the U.S. Naval Observatory. You do not need this test just to extract a year from a date.
YEAR identifies the calendar year rather than automatically assigning your organization’s financial year. A reporting period beginning in July needs a separate mapping. Likewise, a weekly report may need a distinct week-year definition around the December-January boundary.
Exercise at the year boundary
Change A2 from December 31, 2024 to January 1, 2025. B2 should become 2025. Then change only A2’s display format to a year and inspect the formula bar: the source still contains the full date.
On your own data, add a year column and inspect records on both sides of New Year. To total an entire year, you can also use date boundaries from January 1 to the start of the next year, as in the SUMIFS guide. Choose the method that preserves a clear period definition.
Key takeaways
Does yyyy formatting give the same result as YEAR?
The display can look the same, but formatting retains the full date while YEAR returns the numerical year.
Do Excel arrays need ARRAYFORMULA?
No. ARRAYFORMULA belongs to Google Sheets. With Excel dynamic arrays use YEAR(A2:A4); in older versions fill YEAR(A2) down.
Does subtracting year numbers calculate age?
It can overstate age before the birthday. Complete years require the birth date, a reference date and an appropriate interval calculation.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
