Extract Year from Date in Excel: Fix Common Errors

If extracting a year from a date in Excel is not working, first check that the cell contains a real Excel date, then use =YEAR(A2) with the date in A2. If the cell contains date text instead, Excel may return an error or an unexpected result; convert the text to a date before applying YEAR.

The quickest way to extract a year

In Microsoft Excel for Microsoft 365 on Windows, enter the YEAR formula in an empty cell next to the date. For example, if A2 contains the actual date March 8, 2024, enter this in B2:

With the Excel date March 8, 2024 in A2, enter =YEAR(A2) in B2. The result is 2024.

Press Enter, then use the fill handle—the small square at the bottom-right corner of the selected cell—to copy the formula down a column. YEAR returns a number representing the calendar year. It does not change the original date, and it does not return a date value.

If the result looks like a date or displays as a serial number, select the result cell and choose Home > Number Format > General. That changes how the result is displayed; it does not repair an invalid source date.

How Excel stores dates and why that matters

Excel generally stores dates as serial numbers and uses a cell’s number format to display them as dates. A cell showing 3/8/2024 may therefore contain a numeric date value, while a cell that looks identical may contain the text characters “3/8/2024.” YEAR can work with a valid date value, but text is not necessarily interpreted as a date.

This distinction explains why changing the display format alone often does not fix the problem. Formatting a text value as Date does not reliably convert it into an Excel date. Likewise, a date that appears as a number may still be valid: the number format may simply be set to General. YEAR uses the underlying value rather than the way the cell is displayed.

Excel also supports different date systems in workbooks. That can affect the serial number corresponding to a date, particularly when dates are copied between workbooks, but it usually is not the first thing to investigate when YEAR fails. Start by checking whether the source is numeric or text and whether the formula points to the intended cell.

Check the source cell before changing anything

Select the date cell and inspect the formula bar. If the value is left-aligned while nearby dates are right-aligned, that can suggest it is text, although alignment may have been changed manually. A more direct check is to test whether the cell is numeric.

With the suspected date in A2, enter =ISNUMBER(A2) in an empty cell. TRUE means Excel sees a number, which includes a valid date serial; FALSE means the cell is text or another nonnumeric value. This check does not prove that every numeric value is a sensible date, but it quickly separates common text-date problems from other issues.

Also confirm that the formula refers to the intended cell. A formula copied down a table can shift references, and a formula may point to a blank cell or to a header instead of the date. Click the formula cell and inspect the reference highlighted in the formula bar. If the source is in another worksheet, verify the sheet name and cell address too.

Look for leading or trailing spaces, apostrophes, or a leading apostrophe that forces text entry.
Check whether imported dates use a different order, such as day-month-year instead of month-day-year.
Confirm that the cell is not blank, contains an error, or holds a date-like label rather than a date.
Check whether the displayed year is merely hidden by a narrow column or an unusual number format.

Convert text dates into real Excel dates

If the value is text, choose a conversion method that matches the source format. For text dates Excel already recognizes under your regional settings, DATEVALUE can convert the text to a date serial. With a date string in A2 that Excel’s current locale recognizes, enter this in B2:

=YEAR(DATEVALUE(A2)) returns the year as a number. For example, if A2 contains the text 3/8/2024 and Excel interprets it as March 8, 2024, the result is 2024. If your computer interprets that string as 3 August instead, the year remains 2024, but ambiguous dates can produce the wrong month and day. DATEVALUE recognition depends on regional date conventions, so verify the interpreted date when day and month could be swapped.

For a consistent ISO-style text value such as 2024-03-08, you can build a date from its fixed character positions instead of relying on locale recognition. With the text string YYYY-MM-DD in A2, enter this in B2:

=YEAR(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) returns 2024 for 2024-03-08. This approach assumes every value has that exact four-digit-year, two-digit-month, two-digit-day layout. It is not suitable for dates with varying lengths or a different order.

For a column of imported text dates, another option is Data > Text to Columns. Select the column, choose Delimited, select Next twice, set Column data format to Date, choose the order that matches the source data (such as YMD or DMY), and select Finish. Inspect the converted cells before using YEAR, especially if the source contains ambiguous dates. On Mac, the Data tab also provides Text to Columns, though the dialog layout can differ slightly by Excel version.

Common errors and what they usually mean

#VALUE! often means YEAR received text it cannot interpret as a date or another unsuitable value. Check for imported text, spaces, invalid date strings, and formulas that return text. Convert the source using a method appropriate to its format rather than assuming that changing the cell format will fix it.

A four-digit result that seems wrong may come from a date interpreted in the wrong regional order or from a formula referencing the wrong cell. For example, 04/05/2024 is ambiguous: it can mean April 5 or May 4. The year is the same in this example, but other two-digit-year or mixed-format data can be more misleading. Confirm the original data’s date convention and inspect several converted rows.

A serial number instead of a year usually means the formula cell is displaying a number format that is confusing, or the formula is missing. Select the cell and look at the formula bar: if it contains a YEAR formula, set the result format to General. If the cell contains a date serial directly, check whether the formula was entered in a different cell or overwritten.

The formula appears as text instead of calculating can happen if the destination cell is formatted as Text before the formula is entered, or if the formula begins with an apostrophe. Set the cell to General, re-enter the formula, and press Enter. If formulas are being displayed across the sheet, check whether Show Formulas is enabled on the Formulas tab, or press Ctrl+` on Windows to toggle it. The backtick key is often near the top-left of the keyboard; keyboard layouts vary.

Choose the right method for your goal

Use YEAR when you need the year as a number for calculations, sorting, filtering, or grouping. The result 2024 is numeric, so it can be compared with other numbers or used in further calculations. If you need the year to appear as text—for example, to combine it with a label—TEXT can return a formatted text value instead.

With a real Excel date in A2, enter =TEXT(A2,"yyyy") to display the four-digit year as the text “2024.” This is useful for labels, but the result is text rather than a number. Text may sort differently from numbers in some mixed data and is less convenient for arithmetic. If you need numeric year data, use YEAR.

A common misconception is that =RIGHT(A2,4) is a dependable alternative. It is not: a true Excel date is stored as a serial number, not as the displayed date string, so RIGHT may return digits from that serial rather than the visible year. It may appear to work on text dates in one format, then fail when the source format changes. Convert or interpret the value as a date instead.

Make the result dependable in a worksheet

When fixing a large imported list, keep the original column until you have checked the conversion. Add a helper column for the converted date or extracted year, compare representative records with the source, and only then replace or remove the original data if appropriate. This makes it easier to detect whether a conversion interpreted day and month correctly.

For a simple one-date-per-row layout, enter the formula in the first result row and fill it down. If new records will be added regularly, convert the data range to an Excel table using Insert > Table. A formula entered in a table column is generally filled through that column, and new table rows can inherit the calculated-column formula. Check the results after pasting imported data, since pasted values can introduce new text formats.

On Excel for Mac, YEAR and the formulas shown here work the same way. The Mac equivalent of Ctrl+1 for opening Format Cells is Command+1. In Excel for the web, the formulas are also supported, though some ribbon controls and conversion dialogs may be arranged differently from the Windows desktop app.

Frequently Asked Questions

Why does YEAR return #VALUE! for a date that looks correct?
It is often text that resembles a date but is not stored as a date serial, or text that Excel cannot interpret under the current regional settings. Test the cell with ISNUMBER, then convert the text using DATEVALUE or a method suited to the source layout.

Can I extract the year without changing the original date?
Yes. Put YEAR in another cell, such as B2, while keeping the original date in A2. The formula returns a separate numeric year and leaves the source value untouched.

Why does changing the cell to Date not fix the formula?
Number formatting controls how Excel displays values; it does not necessarily turn text into a date serial. Convert the text itself first, then apply a date format if you want to display the converted value as a date.

Does YEAR return a two-digit or four-digit year?
YEAR returns the year as a number, normally shown as a four-digit value such as 2024 under General formatting. If you need a particular text appearance, use a suitable number format or TEXT, remembering that TEXT returns text.

Why does the formula show instead of the answer?
Check whether the cell is formatted as Text, whether the formula begins with an apostrophe, and whether Show Formulas is enabled. Set the cell to General, re-enter the formula, or toggle formula display off.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.