Calendar Year Excel Formula: Extract the Year from a Date

Formulas & functions · Updated November 21, 2026

If a date is in cell A2, use =YEAR(A2) to return its calendar year as a number. For example, if A2 contains July 19, 2024, the result is 2024; the formula works with any valid Excel date.

What the YEAR formula returns

Excel stores dates as serial numbers and displays them in a date format. The YEAR function reads a valid date value and returns its year component as a whole number. It does not change the date in A2: it produces a separate result in the cell containing the formula.

With the date July 19, 2024 in A2, enter this formula in another cell:

=YEAR(A2)

The result is the number 2024. Because it is numeric, you can use it for comparisons, calculations, sorting, or criteria in other formulas. The cell can be formatted as General or Number; a date format is not needed to show the year.

To use the formula in a column, place it beside the first date and fill it down. In Microsoft 365 for Windows, drag the fill handle at the lower-right corner of the cell, or copy and paste the formula into the remaining rows. Excel normally adjusts A2 to A3, A4, and so on as the formula is copied. If you need the formula to keep referring to one particular cell, use an absolute reference such as $A$2.

How to enter and copy the formula

Suppose dates are listed in A2:A20 and you want their calendar years in column B. Select B2, enter the formula below, and press Enter:

With the date to extract in A2, enter =YEAR(A2) in B2; if A2 is July 19, 2024, B2 displays 2024.

Then copy B2 down through B20. In a range formatted as an Excel table, entering the formula in the first row of a new column may automatically fill the calculated column. If that does not happen, copy the formula down manually. The result column can then be filtered or sorted by year without changing the original date values.

The steps are the same in Excel for Mac and Excel for the web: enter the formula in a cell and fill or copy it down. Some interface details and keyboard shortcuts differ, but the YEAR function and its syntax are the same across these versions.

Show a year as text instead

YEAR returns a number, which is usually the best choice for calculations. If you specifically need a four-digit year as text—for example, to combine it with a label—you can use TEXT. With a real date in A2, enter =TEXT(A2,"yyyy"); for July 19, 2024, the result is the text value 2024.

The visible result may look identical, but text and numbers behave differently. Text may sort differently from numbers and may not work as expected in arithmetic or numeric criteria. Use YEAR when you need to calculate or group by year. Use TEXT when the year is part of a display string, such as a report heading. For example, a label can be built by joining text and a year, but keep a separate numeric year column if you also need to analyze the data.

Another option is to leave the date intact and change its display format. This is useful when you want the cell to remain a date for date calculations. Select the date cells and use the Number Format controls on the Home tab to choose a suitable date display. A custom format such as yyyy displays only the year, but the underlying value remains the full date. That is different from extracting the year into a new cell.

Return the first or last date of a calendar year

Sometimes a worksheet needs a year boundary rather than the year number. With a date in A2, use =DATE(YEAR(A2),1,1) to return January 1 of that date’s year. If A2 is July 19, 2024, the result is January 1, 2024. Format the result as a date if Excel shows a serial number.

To return December 31 of the same year, with the date still in A2, use =DATE(YEAR(A2),12,31). For July 19, 2024, the result is December 31, 2024. These formulas can help create clear start and end boundaries for reports. Using DATE also avoids typing a year boundary as text, which could be interpreted differently under different regional date settings.

When filtering timestamps that may include times of day, a start-inclusive and next-year-exclusive range is often more reliable than an end date of December 31. A timestamp on December 31 at 3:00 p.m. is later than midnight at the start of that date. The next-year boundary method below includes every time on the final day.

Count records in a calendar year

Assume transaction dates are real Excel dates in A2:A6, and E1 contains the year 2024. If the five dates are December 31, 2023; January 1, 2024; July 19, 2024; December 31, 2024; and January 1, 2025, enter this formula to count dates in 2024:

=COUNTIFS(A2:A6,">="&DATE(E1,1,1),A2:A6,"<"&DATE(E1+1,1,1))

The result is 3: the January 1, July 19, and December 31 dates in 2024. COUNTIFS applies two conditions to the same date range: the date must be on or after January 1, 2024, and it must be before January 1, 2025. This includes timestamps on December 31, 2024, regardless of their time, while excluding midnight on January 1, 2025.

This approach also makes the formula easy to reuse. Change the year in E1 and the boundaries update automatically. If your data contains blank cells or text that only looks like a date, check those values: they may not be counted as intended because they are not valid date values.

Sum values for a calendar year

If transaction dates are in A2:A6, corresponding amounts are in B2:B6, and E1 contains 2024, use the same date boundaries with SUMIFS:

=SUMIFS(B2:B6,A2:A6,">="&DATE(E1,1,1),A2:A6,"<"&DATE(E1+1,1,1))

For an illustrative set of amounts of 10, 20, 30, 40, and 50 in the same order as the five dates above, the result is 90: Excel adds 20, 30, and 40, the amounts paired with dates in 2024. The date range and sum range must cover matching rows. If one range starts or ends on a different row, the formula may return an error or an incorrect total.

Using date boundaries is generally preferable to creating a year column just for one summary, especially when the source dates include times. A helper column containing YEAR results can still be useful when you need to inspect, filter, or repeatedly group a large dataset by year.

Calendar years, current year, and fiscal years

A calendar year runs from January 1 through December 31. If you want the current year without referring to a date cell, enter =YEAR(TODAY()) in any cell. It returns the year of the current date according to Excel’s date calculation and updates when the workbook recalculates. This is convenient for a dashboard, but it means the displayed year can change over time; it is not a fixed record of when the workbook was created.

A fiscal year may start in a month other than January, so the calendar-year formula might not match the organization’s reporting label. For a fiscal year beginning April 1 and named for the year in which it ends, with a date in A2, use =YEAR(A2)+(MONTH(A2)>=4). A date of March 31, 2024 returns 2024, while April 1, 2024 returns 2025. The comparison in the formula evaluates as TRUE or FALSE, which Excel treats as 1 or 0 in this addition.

That example is only one naming convention. Some organizations label a fiscal year by the year in which it begins, and other start months require a different boundary. Confirm the reporting convention before adapting a formula; the arithmetic can be correct while the chosen fiscal-year label is wrong for a particular organization.

A related source of confusion is the ISO week-year. Near New Year’s Day, an ISO week date can belong to a week-year different from the calendar year returned by YEAR. For ordinary dates and calendar-year reports, YEAR is appropriate. For week-based reporting, use a method designed for ISO weeks rather than assuming the calendar year and week-year always match.

Common problems and how to fix them

  • The formula returns a strange number or an error: Check that the referenced cell contains a valid Excel date, not text that merely resembles one. A date imported from another system may need to be converted to a real date value. Regional settings can affect how text dates are interpreted, so do not assume that a string such as 04/05/2024 means the same month and day everywhere.
  • The result displays as a date: YEAR returns a number, but the result cell may have inherited a date format. Change the result cell’s number format to General or Number to display the year as 2024.
  • The year looks right but totals are wrong: Check whether the original date cells include times, and use a boundary that is less than January 1 of the following year. Also verify that date and amount ranges align and that the year criterion cell contains a number.
  • The formula changes when copied: Relative references move as formulas are filled down. That is usually correct for a date reference such as A2. To keep a criterion year fixed in E1 while copying, use $E$1 in the formula.
  • Dates shifted after copying between workbooks: Excel workbooks can use different date systems. If dates came from another workbook and appear offset, check the workbook’s date-system settings and the import process before interpreting the YEAR results.

YEAR itself does not need a special regional formula name or a different argument separator in standard English-language Excel. If a workbook uses localized function names or list separators, Excel may display formulas according to the application’s language and regional settings, but the underlying goal remains the same: supply a valid date value and extract its year.

Frequently Asked Questions

What is the formula to get the year from a date in Excel?
With the date in A2, enter =YEAR(A2). It returns the year as a number, such as 2024 for a date in 2024.

How do I get the current year in Excel?
Enter =YEAR(TODAY()). It returns the year for the current date and can update when Excel recalculates the workbook.

Why does YEAR return a number instead of a date?
That is the function’s purpose: YEAR returns the year component as a number. If the cell is formatted as a date, change its display format to General or Number to see the year clearly.

How do I count dates in a particular calendar year?
Use COUNTIFS with a start date on January 1 and an exclusive end date on January 1 of the next year. This method counts dates with times on the final day as well as dates stored without times.

Does YEAR return the fiscal year?
No. YEAR returns the calendar year of the date. For a fiscal year, build a formula around the organization’s actual start month and confirm whether its reporting label uses the start year or end year.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.