YEAR Formula Not Working? Excel Fixes

If Excel’s YEAR formula is not working, first check that its reference contains a real Excel date, not date-looking text, and that the formula is entered correctly. For a valid date in A2, enter =YEAR(A2); if A2 contains text or the result looks like a date instead of a year, the fixes below explain what to check.

What the YEAR function does

YEAR returns the year component of a date as a number. Excel stores genuine dates as serial numbers, so the function reads the date value rather than simply taking the last four characters displayed in a cell. For example, a cell showing June 15, 2024 can contain a date serial underneath; YEAR returns 2024.

With the date June 15, 2024 stored as a real date in A2, this formula returns the number 2024:

=YEAR(A2)

The formula does not create a date, change the source cell, or guarantee that the output will be displayed as four digits. Its result is a number. That distinction explains several common surprises: text that looks like a date may not be recognized, and a result cell formatted as a date can display an unexpected month and day.

Check the formula and its cell reference

Start with the basics. The function name is YEAR, followed by parentheses containing one date value or a reference to a cell with a date. A missing parenthesis, misspelling such as YER, or reference to the wrong cell can cause an error or an incorrect result. If copying the formula down a column, make sure each row points to the matching date.

With a genuine date in A2, enter the formula in B2, then use the fill handle to copy it down alongside the dates. In B2, the result should be the year belonging to A2. If your first date is in a different cell, replace A2 with that cell reference. YEAR takes one argument, so it does not need a comma-separated list of inputs.

Also check for a leading apostrophe or an equals sign stored as text. If a cell displays the characters =YEAR(A2) rather than a result, Excel may be treating the entry as text. Select the formula cell, change its number format to General, then press F2 and Enter on Windows to make Excel evaluate it. On Mac, use Control+U to edit the cell, then press Return; shortcut behavior can vary with keyboard settings.

Make sure the source is a real date, not text

A date imported from a website, CSV file, or another system may look like 6/15/2024 but be stored as text. Formatting that cell as a date does not necessarily convert text into a date value. YEAR can then return #VALUE! because it cannot interpret the text as a valid date in the workbook’s regional settings.

Useful clues include a warning marker, a leading apostrophe, or text that stays unchanged when you apply a different date format. Alignment is only a clue, not proof, because cells can be manually aligned. You can also select the source cell and inspect the formula bar; a text value may include spaces or other characters that are hard to notice in the grid.

If A2 contains the text June 15, 2024 and the workbook is using an English locale that recognizes that wording, this formula returns 2024:

=YEAR(DATEVALUE(A2))

DATEVALUE converts recognizable date text into an Excel date value, which YEAR can then read. The exact text DATEVALUE recognizes depends on regional settings: 04/05/2024 could mean April 5 or May 4. For that reason, do not assume a formula will interpret ambiguous imported dates correctly. Confirm the source’s date convention before converting a large range.

For many imported columns, Excel’s Text to Columns tool can convert date text in one operation. Select the affected column, choose Data > Text to Columns, select Delimited and continue through the wizard, then set the column data format to Date and choose the order that matches the source, such as YMD or MDY. Finish and check several converted values. If that conversion option is unsuitable, re-import the data with the correct locale or use a carefully checked conversion process.

Fix results that look like dates or show the wrong year

YEAR returns a number such as 2024, but Excel applies number formatting separately. If the result cell is formatted as Date, Excel may interpret 2024 as a date serial and show a date in 1905 rather than the digits 2024. The calculation may be correct even though the display is confusing.

Select the result cells and choose Home > Number Format > General or Number. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, then choose General or Number. The cell should display the year as a number. Conversely, if you want the original date to remain intact but only want its year visible, apply a custom format of yyyy to the date cell. That changes its appearance, not its underlying value.

A different apparent wrong-year problem occurs when the source date itself is incorrect. Check the full date, including month and day, in the formula bar or temporarily apply a full date format. YEAR cannot infer what an ambiguous date was intended to mean; it returns the year associated with the value Excel actually has.

Understand errors and other unexpected behavior

#VALUE! commonly points to invalid or unrecognized text, an empty string, or a value that is not a usable date. Inspect the exact source contents, including leading or trailing spaces and characters copied from another system. A blank cell is not the same thing as a valid date, and formulas that return an empty string can also behave differently from actual blank cells.

#NAME? usually means Excel does not recognize the function name or part of the formula. Check the spelling, make sure the formula begins with an equals sign, and remove accidental punctuation. If the cell displays the formula rather than a result, check that it is not formatted as Text and that Show Formulas is not turned on. In Excel for Microsoft 365 on Windows, Ctrl+` toggles formula display; the grave-accent key may be in a different place on some keyboards.

If a valid date is present but the result does not update after you change the source, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may wait for a recalculation. Press F9 on Windows to recalculate; on Mac, use the recalculation shortcut configured for your Excel version and keyboard.

Finally, check that the formula points to the intended date cell and that the source was not changed during sorting, pasting, or importing. A technically valid formula can still return the wrong year if its reference is one row off or points to an unrelated column.

How Excel date systems affect YEAR

Excel stores dates as sequential numbers, with the displayed calendar date controlled by the cell’s number format. Most Windows workbooks use the 1900 date system; some workbooks, often created for compatibility with older Mac spreadsheets, use the 1904 date system. The same serial number can represent different calendar dates in the two systems.

This matters most when moving date values between workbooks or copying data from older files. If a date appears to shift by several years after a transfer, inspect the dates themselves and the workbook’s date-system setting rather than changing YEAR. The function reports the year encoded by the date value it receives; it does not detect what an external source intended. Avoid switching a workbook’s date system casually, since doing so can change how existing serial values are interpreted.

When a different approach is more suitable

Use YEAR when you need a numeric year for grouping, comparisons, or further calculations. If the goal is only to show a complete date with a different appearance, use cell formatting instead; changing the date to text can make later sorting and calculations less reliable.

If you need a year label as text, for example to combine it with other text, a text-formatting function may be appropriate. But text is not interchangeable with the number returned by YEAR: sorting, comparisons, and arithmetic can work differently. For normal date analysis, retain genuine date values and use YEAR only where a separate year number is useful.

For a full column, enter the formula beside the first date and fill it down, then check the first, last, and a few middle rows. If the range is an Excel table, entering the formula in a table column usually fills the calculated column automatically. Confirm that this has happened and that the references line up with the corresponding dates before relying on the results.

A quick troubleshooting order

Working through checks in order helps distinguish a bad formula from a bad source value. Try these steps:

Confirm the formula spells YEAR correctly and refers to the intended cell.
Check whether the source is a genuine date or date-looking text, and inspect for spaces or apostrophes.
Convert text using the source’s known date order; do not guess when dates are ambiguous.
Set the result cell to General or Number if it is displaying as a date.
Check calculation mode if changes are not reflected, and recalculate the workbook.
Verify several source dates against the expected calendar dates before filling or using the results elsewhere.

This sequence also helps when the formula returns a plausible but incorrect year. First verify the stored date and the reference; only then investigate workbook date systems or data-import settings. Changing the formula cannot repair a source date that was parsed incorrectly.

Frequently Asked Questions

Why does YEAR return #VALUE!?
Excel usually cannot interpret the referenced value as a date. It may be text in an unsupported format, contain extra characters, or be a formula result that is not a valid date. Check the source and convert it using the correct regional date order.

Why does YEAR show 1905 instead of 2024?
The result cell is probably formatted as a date, so Excel is displaying the number 2024 as a date serial. Change the result format to General or Number to display the year number.

Can YEAR extract a year from text?
Only when the text can first be interpreted as a date. DATEVALUE can help with recognizable text, but its interpretation depends on locale. Confirm ambiguous dates rather than assuming their month and day order.

Why is my formula visible instead of its result?
The cell may be formatted as Text, the entry may have a leading apostrophe, or Show Formulas may be enabled. Change the cell to General, re-enter the formula, and check whether formula display is toggled on.

Does YEAR change the original date?
No. YEAR returns a separate numeric year value and leaves the source date unchanged. If you only want to display the date’s year while retaining the date, apply the custom number format yyyy to the date cell.

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.