Find Year in Excel: Extract a Year from a Date

To find the year in an Excel date, enter =YEAR(A2) in an empty cell, replacing A2 with the cell containing your date, then press Enter. For example, if A2 contains 15-Mar-2024, the formula returns 2024. This extracts the year as a number, which you can use in calculations, sorting, summaries, or filters. You will need a workbook with dates Excel recognizes as dates; if your dates are stored as text, see the troubleshooting section.

What You’ll Need

A workbook in Excel for Microsoft 365 on Windows, with the date or dates you want to work with.
An empty column or cell for the year results. Keep the original date column if you may need to check or reformat the dates later.
For a list, a header row such as Date in the source column and Year in the new column. Clear labels make it easier to sort and filter correctly.

Step-by-Step

Check the date column. Find the cells containing the dates. In the example used below, the first date is in A2, with additional dates underneath it. Click a date cell and look at the formula bar: a value Excel recognizes as a date will usually appear there as a date or a date serial value. The way the date looks on the worksheet depends on its number format, so appearance alone does not always tell you how Excel stores it.
Add a column for the year. If your dates are in column A, add a heading such as Year in B1. Keeping the year in a separate column preserves the original dates, and makes the results easier to filter, count, or use in a PivotTable. If you only need the year from one date, you can put the formula in any empty cell instead.
Enter the YEAR formula. With the date in A2, enter =YEAR(A2) in B2 and press Enter. Excel returns the calendar year as a number. For example, if A2 contains 15-Mar-2024, the result in B2 is 2024. The formula reads the underlying date value, not the way the date is displayed, so it works whether A2 appears as 15-Mar-2024, 3/15/2024, or another recognized date format.
Copy the formula down the list. Select B2, then point to the small square at the lower-right corner of the selected cell, called the fill handle. Drag it down alongside the remaining dates. You can also double-click the fill handle when there is a continuous column of neighboring data for Excel to follow. Check the last few rows to confirm the formula has extended to every date. Excel adjusts the reference for each row, so the next result refers to A3, then A4, and so on.
Check and use the results. Compare a few years with their original dates. The results should be four-digit years for ordinary modern dates. Because YEAR returns numbers, you can sort the Year column from oldest to newest or use it in summaries. To filter a table to a particular year, select a cell in the table, choose Data > Filter if filters are not already on, open the Year column’s filter arrow, and select the year you want. Clear the filter when you want to see all rows again.
Display a date as a year without extracting it. If the goal is only to show a date cell with its year visible, select the date cells and press Ctrl+1. In Format Cells, choose Number > Custom, enter yyyy in the Type box, and select OK. The cell still contains the complete date; only its display changes. On Mac, use Command+1 to open Format Cells. This display-only option is useful for a compact report, but it does not create a separate year value for filtering or calculations.

For example, a date displayed as 15-Mar-2024 will appear as 2024 after applying the custom format yyyy. If you later change the format back to a date format, the month and day will show again. Use the YEAR formula instead when you need the year in its own cell.

Common Mistakes

Using a date that is actually text. A date typed or imported as text may look normal but not behave like a date. YEAR can return #VALUE! for text it cannot interpret. Convert or re-import the value as a date before relying on the result.
Changing the date format when you need a year value. Applying yyyy makes the cell look like a year, but the underlying value remains the full date. Use the YEAR function when the year must be a separate number for grouping, formulas, or filtering.
Leaving the wrong cell reference in the formula. If your date is in C2, a formula that refers to A2 will return a result based on A2 instead. Confirm that the cell reference inside the parentheses points to the date you intend to use.
Including a header in the formula fill. Start the formula beside the first date row, not beside the column heading. Otherwise, the formula may attempt to interpret the heading as a date.
Expecting YEAR to return a text label. YEAR returns a numeric value. That is normally helpful for sorting and calculations. If you only need a visual label, format the result cell as General or Number; avoid converting it to text unless a specific text output is required.

Tips

Use an Excel table for growing lists. Select the date data and choose Insert > Table, then add the Year heading and formula in the new column. Excel generally fills a calculated column down the table and carries it into new rows, so you do not have to repeatedly drag the formula.
Keep the source date intact. A separate year column is easier to audit than replacing dates with year-only values. The original date may be needed later to calculate elapsed time, sort by month, or check the source record.
Use real year numbers for chronological sorting. The YEAR results are numbers, so 2019 sorts before 2020. If a year has been imported or converted to text, sorting can behave unexpectedly, especially when values have inconsistent formats.
Choose the right method for the job. Use YEAR to extract a usable year, a yyyy number format to change only how dates appear, and a filter on the year column to show records from a selected year. These approaches solve different problems even though they can all make a year visible.
Check regional date conventions. A value such as 04/05/2024 can mean April 5 or May 4 depending on the regional date settings used when it was entered or imported. Confirm the intended date before extracting its year; the year may be right even when the month and day have been misread.

Troubleshooting

The formula displays #VALUE!. The referenced cell may contain text, an invalid date, or extra characters from an import. Try selecting the source cell and checking whether it can be formatted as a date. If dates were imported from a file, use Data > Text to Columns to review the import steps and choose the correct date order when that option is available. Another option is to re-import the source with the correct regional date settings. Make a copy before changing a large imported column.

The formula displays a number that looks like a date. YEAR normally returns a four-digit year, but the result cell may have inherited a date number format. Select the result cells, open the Home tab’s Number Format list, and choose General or Number. The underlying year result does not change; this corrects how Excel displays it.

The result shows 1900 or another unexpected year. Excel stores recognized dates as serial numbers. A blank cell referenced by the formula, or a cell containing zero, can produce a result associated with Excel’s date system rather than a meaningful date. Check that the formula points to the right row and that the source cell is not blank or zero. Also check for dates that were imported with the wrong day/month order or year.

Some rows calculate and others fail. The column may mix real Excel dates with text dates, blanks, or values in different formats. Inspect the rows that fail and compare them with rows that work. Standardize the source column first, then copy the formula down again. If the formula itself is visible instead of a result, select the formula cell and check that Show Formulas is off under the Formulas tab; also confirm the cell is not formatted as Text, then re-enter the formula.

The filter does not show a year option. If you are filtering the original date column, Excel may present individual dates or date-filter options rather than the simple year list you expect. Add a YEAR helper column, make sure the results are numeric, and filter that column. In an Excel table, use the filter arrow in the Year header and select the required year.

Frequently Asked Questions

How do I get the year from a date in Excel?
Put the date in a cell such as A2, then enter =YEAR(A2) in another cell. If A2 contains 15-Mar-2024, the result is 2024.

Can I find the year without a formula?
Yes. To show a date as a year, select it and choose Ctrl+1, then Number > Custom and enter yyyy. This changes the display only; the complete date remains stored in the cell.

How do I extract the year for every row?
Enter the YEAR formula beside the first date, then use the fill handle to copy it down. In a table, entering the formula in the Year column usually fills the calculated column automatically.

Does YEAR work if my date includes a time?
Yes. Excel dates can include a time portion, but YEAR returns the year component of the date. For example, a valid date-and-time value on 15-Mar-2024 still returns 2024.

Why does Excel return the wrong year for a date?
Check the source cell and its regional interpretation. A date imported with the wrong day/month order, a text value interpreted unexpectedly, or a formula pointing to another row can produce a year that does not match the intended record.

Can I use the year to count records?
Yes. Once the year is in its own column, you can filter or summarize that column, including in a PivotTable. Keep the year results numeric and verify that the source dates are correct before using the counts.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.