Excel YEAR Function Not Working? Fix Dates and Results

If Excel’s YEAR function is not working, first check whether the referenced cell contains a real Excel date or text that only looks like one. With a valid date in A2, the basic formula is =YEAR(A2); if A2 is text, convert it to a date before extracting the year. The fixes below help distinguish a bad input from a display, formula, or import problem.

What the YEAR function returns

YEAR returns the year number associated with an Excel date, such as 2024 for March 15, 2024. It takes one argument: a date value. In a worksheet, dates are stored as serial numbers, while their familiar appearance—such as 3/15/2024—is controlled by cell formatting. That distinction is central to troubleshooting: a cell can look like a date without containing a date value Excel recognizes.

With the date March 15, 2024, in cell A2, the formula is =YEAR(A2), and the result is the number 2024. The result is numeric, not the text string “2024.” YEAR also works with date-and-time values: it uses the date portion and does not return or change the time. Formatting A2 to display a different date style does not change the year that YEAR reads.

Do not use YEAR to extract a year from a cell that contains only the number 2024. Excel may treat that number as a date serial rather than as a calendar year, so the result will not be 2024. If the cell already contains the year number you need, use that number directly. YEAR is for dates, not for interpreting every number or text value as a year.

Check whether the cell contains a real date

Start by selecting the source cell and looking at its contents in the formula bar. Then test its type. With the suspected date in A2, enter =ISNUMBER(A2) in another cell. TRUE indicates that A2 contains a number, which is how Excel stores valid dates; FALSE indicates that it is text or another nonnumeric value. This test does not prove that every number is a sensible date, but it quickly identifies many imported-text problems.

With the suspected value in A2, you can also enter =ISTEXT(A2) in a spare cell. TRUE confirms that Excel sees the value as text. Left alignment can be a clue that a date is text, but it is not a dependable test because alignment can be changed manually. The ISNUMBER and ISTEXT results are more useful than appearance alone.

If A2 is numeric and YEAR still returns an unexpected year, inspect the actual date value. A cell displaying only “2024” might be formatted to hide the month and day, or it might contain the serial number 2024. Change the cell’s format temporarily to a date using Home > Number Format, then inspect what date appears. Restore the original format afterward if needed.

Convert dates imported as text

Text dates often arrive from CSV files, websites, databases, or copied reports. A value such as 15/03/2024 may be understood differently depending on the computer’s regional date settings. If Excel cannot interpret the text, YEAR cannot reliably extract its year. If it does interpret it differently than intended, the formula can return a valid-looking but incorrect result.

For a text date in A2 that Excel recognizes under the current regional settings, enter =DATEVALUE(A2) in a spare cell. DATEVALUE converts recognized date text to an Excel date serial. Format the result cell as a date to check it before relying on its year. With “March 15, 2024” in A2 and a locale that recognizes that wording, the result represents March 15, 2024. DATEVALUE is not a universal parser: ambiguous strings such as 03/04/2024 may be read as March 4 or April 3 depending on settings.

For a fixed-width text value in A2 written exactly as YYYY-MM-DD, such as 2024-03-15, you can build a date from its parts instead of relying on locale-based interpretation. Enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) in a spare cell. The result is an Excel date for March 15, 2024; format the result as a date to see it. This method assumes the text always has four year digits, two month digits, two day digits, and hyphens in those positions. It is not appropriate for dates with a different layout or inconsistent spacing.

To convert a whole imported column without a formula, select the column and choose Data > Text to Columns. In the wizard, choose Delimited, select Next, leave delimiters unselected, and select Next again. Under Column data format, choose Date and the input order that matches the source data, such as YMD for year-month-day, then select Finish. Check several converted dates, especially those where day and month could be confused. Keep a copy of the original data until the results are verified.

Check formula entry and calculation

If the cell shows the characters =YEAR(A2) rather than a result, Excel may be treating the entry as text. Check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign in the formula bar. Change the cell format to General, then re-enter the formula; simply changing the format may not convert an existing text entry. Select the cell, press F2, and then press Enter on Windows. On Mac, use Control+U to edit the active cell, then press Return.

Another possibility is that Show Formulas is turned on, which displays formulas across the worksheet instead of their calculated results. On Windows, Ctrl+` toggles this view; the key is usually above Tab. You can also choose Formulas > Show Formulas. On Mac, use Formulas > Show Formulas if the keyboard shortcut differs on your keyboard. This setting changes what is displayed, not the formula itself.

Excel normally calculates formulas automatically, but a workbook can be set to manual calculation. If results seem stale after changing a source date, check Formulas > Calculation Options > Automatic. Press F9 on Windows to recalculate; on Mac, use Formulas > Calculate Now if needed. Calculation settings can affect other formulas in the workbook, so consider the workbook’s intended setup before changing them.

Understand common errors and unexpected results

A #VALUE! result commonly means the input is text Excel cannot interpret as a date, or that a referenced cell contains an error. Trace the reference and inspect the input rather than hiding the error immediately. A blank A2 can also produce a surprising year because Excel may treat a blank reference like zero in date calculations. If blank rows should stay blank, guard the calculation.

With a date in A2 and an empty cell where no result should appear, enter =IF(A2=",",YEAR(A2)) is not a valid way to write the blank check because its quotes and arguments would be incomplete. Use the valid formula =IF(A2="","",YEAR(A2)) instead. It returns an empty display when A2 is blank and otherwise returns the year. The formula assumes that nonblank entries in A2 are valid dates; it does not repair text dates.

If YEAR returns a year that is off by one or several years, confirm the source date and regional interpretation, not just the formula. For example, text “04/05/2024” can represent different dates in different date conventions, though both interpretations happen to have the same year. A string such as “12/31/2023” may fail in a setup expecting day/month/year. Dates copied between workbooks can also be affected by different date systems; if a whole set of imported dates shifts, investigate the workbook and source system rather than adjusting YEAR.

Separate the year value from its display

YEAR returns a number, so the result cell’s formatting determines how that number appears. A result of 2024 may display as 1,904 or as a date if the cell has an unsuitable number format. Select the result cell and choose Home > Number Format > General or Number to see the year as a number. This changes presentation only; it does not alter the source date.

If your goal is to show the year as part of a date, you may not need YEAR at all. Apply a date format that displays the year, or use a custom date format such as yyyy. If you need a text label for a report, a text-formatting function may be more appropriate, but text is less convenient for later arithmetic and sorting than a numeric year. Choose based on whether the next step needs a number, a date, or display-only text.

Choose the right approach for your data

Use YEAR when each source cell is a valid date and you need a numeric calendar year—for example, to group transaction dates by year. If you have a text date, convert it first and verify that Excel interpreted the month and day correctly. If you have a year number already, use it as-is rather than treating it as a date. If you only want a date to look like it includes a year, change its display format instead of creating a separate value.

For a list of dates, put the formula beside the first date and fill it down only after confirming the source column is consistent. On Windows, drag the fill handle or double-click it to copy a formula down adjacent data; Mac behavior is similar. Check the first, a middle, and the last few results, and watch for blank cells, text entries, or dates imported in a different order. A formula that works for one row can still produce errors for inconsistent rows elsewhere in the column.

Frequently Asked Questions

Why does YEAR return 1900 for a blank cell?
A blank reference can be treated like zero in date calculations, and zero corresponds to the beginning of Excel’s 1900 date system. If a blank row should show nothing, use the blank-check formula above, and ensure actual input rows contain valid dates.

Why does YEAR return 1905 when the cell says 2024?
The cell may contain the number 2024, which Excel interprets as a date serial, rather than a date in the year 2024. Check the underlying value and format. If 2024 is already the year you want, use the number directly.

Can YEAR work with a date stored as text?
Only when Excel can interpret that text as a date in the current context. For dependable results, convert the text to a real date first, using DATEVALUE for recognized text or a parsing method suited to the source’s exact layout. Verify ambiguous dates before using them.

Does changing the date format fix YEAR?
No. Formatting changes how a value appears, not whether text becomes a date. Format a valid date to display it differently; convert text dates before asking YEAR to extract their year.

Why is my formula displayed instead of its result?
Check for Text formatting, a leading apostrophe, or Show Formulas mode. Set the cell to General and re-enter the formula if it is stored as text, or turn off Show Formulas from the Formulas tab if formulas are being displayed throughout the sheet.

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.