YEAR Function in Excel Not Working? Fixes That Help

If Excel’s YEAR function is not working, first check that its argument is a real Excel date, not text or a number that only looks like a year. With an actual date in A2, such as June 15, 2024, enter =YEAR(A2); the result should be 2024. If the result is an error or an unexpected year, use the checks below to identify what Excel is reading in the cell.

What the YEAR function does

YEAR returns the year portion of a date as a number. Excel stores most dates as serial numbers: the number represents a day, and the cell’s number format controls whether you see a date, a number, or another display. The function reads that date value; it does not simply extract four digits from whatever is visible in the cell.

For example, with a true date of June 15, 2024 in A2, =YEAR(A2) returns 2024 whether A2 is displayed as 6/15/2024, June 15, 2024, or a serial number. Changing the display format does not change the underlying date. This distinction explains many reports that YEAR is “wrong”: the cell may look like a date or year while storing something else.

Excel’s standard Windows date system begins at January 1, 1900, and workbooks can also use the 1904 date system. The setting affects the serial values and can create a 1,462-day difference when dates move between workbooks configured differently. It is rarely the right fix for a single YEAR formula, so do not change the workbook’s date system just to correct one result.

Check the cell value and format

Start by clicking the referenced cell and looking at the formula bar. A date may appear as text, may contain a time, or may be a plain number. Then temporarily choose General from Home > Number Format. A genuine date will usually display as a serial number; that confirms Excel holds a numeric date value, though it does not prove the date is the one you intended. Restore a suitable date format afterward if needed.

To display a date as a date, select the cell and choose Home > Number Format > Short Date. On Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 opens the equivalent dialog. Formatting a numeric date this way can correct its appearance, but formatting text as a date cannot turn the text into a usable date value.

Also check the formula reference. If the formula says A2 but the intended date is in B2, YEAR may be working correctly on the wrong cell. Click the formula cell and inspect the colored reference outline, or select the formula and verify which cell address is highlighted.

Convert dates stored as text

Imported spreadsheets, copied web tables, and CSV files often contain date-looking text. Clues include values aligned like text, a leading apostrophe, or an error when you use date calculations. Text such as 6/15/2024 is also ambiguous: one computer may read it as June 15, while another regional setting may interpret the day and month in a different order.

For a simple text date that matches the computer’s regional date convention, DATEVALUE converts recognized date text to an Excel date serial. With the text 6/15/2024 in A2 and Windows set to a month/day/year locale, =DATEVALUE(A2) returns the serial for June 15, 2024 (45458 in Excel’s 1900 date system). Format the result as a date to view it as a calendar date.

You can combine conversion and extraction. With that same text in A2, =YEAR(DATEVALUE(A2)) returns 2024. DATEVALUE depends on whether Excel can interpret the text under the current regional settings; it is not a reliable universal parser for ambiguous strings. If imported data mixes date conventions, confirm the intended order before converting, rather than accepting a plausible but incorrect date.

For a one-time conversion, select the affected column and use Data > Text to Columns, then follow the wizard and choose the correct date order on its final step. This changes the selected data, so preserve a copy first. The menu labels can vary slightly between Excel versions and platforms. In Excel for the web, some conversion and import options differ from desktop Excel; if the wizard is unavailable, clean or convert the data in desktop Excel or correct it at the source.

Why YEAR returns an unexpected year

A common misconception is that YEAR extracts a year from any four-digit value. It does not. If A2 contains the number 2024, Excel treats it as a date serial when a date function uses it, not as the year 2024. A serial near 2,024 represents a date in 1905 in the standard date system. If a column already contains year numbers, use those values directly instead of applying YEAR.

Another cause is a date with the wrong interpretation. For instance, text 03/04/2024 could mean March 4 or April 3, depending on the source and locale. YEAR returns 2024 either way, so the year may appear correct while month and day are wrong. For dates near New Year, an incorrect day/month interpretation or a shifted source date can also place a record in the wrong year.

Time values do not normally cause a problem: a valid Excel date-time has a whole-number date part and a fractional time part, and YEAR reads the date. A blank cell, however, can behave like zero in some calculations and produce a misleading early year rather than a blank. If empty rows should stay empty, with a date or blank in A2, =IF(A2=",",YEAR(A2)) is not valid syntax, so do not use it. Instead use the valid formula =IF(A2="","",YEAR(A2)); it returns a blank when A2 is empty and otherwise returns its year. This assumes A2 contains a real date when it is not blank.

Understand common errors

#VALUE! usually means Excel cannot interpret the supplied value as a date or number acceptable to YEAR. Check for unrecognized text, stray spaces, or an imported value with a format that conflicts with your regional settings. A cell formatted to look like a date can still contain text, so changing the format alone may leave the error unchanged.

#NAME? can mean the function name was misspelled, or that the formula contains text Excel treats as an undefined name. In English-language Excel, the function is YEAR. If a workbook was created in another language or uses localized formula names, Excel’s formula entry and translation behavior may differ. Also inspect the formula for missing parentheses or an incorrect cell reference.

For a visible diagnostic rather than an error, with a valid date or potentially invalid text in A2, =IFERROR(YEAR(A2),"Check date") returns the year when Excel can calculate it and the words Check date when the calculation errors. This can help flag a column for review, but it does not repair bad source data; a text value that Excel silently interprets as a date could still return a year.

When the formula does not update

If the date changes but the YEAR result stays the same, check calculation mode. In desktop Excel for Windows, select Formulas > Calculation Options > Automatic. Manual calculation can leave formula results stale until the workbook recalculates. After changing the setting, save and verify a few results.

Also look for a leading apostrophe or an equals sign stored as text. If a cell displays the literal characters =YEAR(A2) instead of a result, change the formula cell’s format to General, then re-enter the formula. A leading apostrophe can force Excel to display an entry as text. The Show Formulas option can also make formulas appear in cells instead of results; turn it off from Formulas > Show Formulas, or use Ctrl+` on Windows. Keyboard behavior can vary with keyboard layout; Mac shortcuts differ, so use the ribbon command if needed.

If the calculation is correct but the displayed result is not, examine the result cell’s number format. YEAR returns a number such as 2024; a custom format could display that number in an unexpected way. Choose General or Number under Home > Number Format to inspect it. Avoid confusing the format of the date input with the format of the year result: they are separate cells and separate settings.

A practical troubleshooting sequence

Use this order to avoid changing data unnecessarily:

Confirm the formula points to the intended cell and starts with an equals sign.
Inspect the input in the formula bar and check whether it is a real date, text, or a year-only number.
Temporarily display the input as General to see whether it is stored as a serial number.
If it is text, identify the source’s date order and convert it with an appropriate method; do not guess when dates are ambiguous.
Set the formula result to General or Number and make sure calculation is Automatic.
Test a known date, such as June 15, 2024, which should return 2024, then check several rows including dates around December and January.

For larger imports, it is usually better to fix the date column once than to wrap every downstream formula in error handling. Keep an untouched copy of the source data, convert a sample first, and compare a few converted dates against the original system or report. That catches silent month/day swaps that an error message would not reveal.

Frequently Asked Questions

Why does YEAR return 1905 instead of 2024?
If the referenced cell contains the number 2024 rather than a date in 2024, Excel interprets that number as a date serial. YEAR extracts the year of the corresponding serial date, not the digits of the number. Use the existing year value directly, or store a full valid date if you need date calculations.

Why does YEAR show #VALUE! for a date-looking cell?
The cell may contain text that Excel cannot parse under the current regional settings, or it may include unwanted characters. Check the formula bar, confirm the source’s date order, and convert the value to a true date. Applying a date format by itself changes display, not text into a date.

Can I get a year from a date that is stored as text?
Yes, if Excel can interpret the text. With a text date in A2 that follows the computer’s regional convention, =YEAR(DATEVALUE(A2)) returns its year. For ambiguous dates, confirm the intended month and day first; a formula cannot know which interpretation the source intended.

Does changing the date format fix YEAR?
Only when the cell already contains a numeric date and the issue is how it appears. Number formatting does not convert text to a date and does not change the value that YEAR reads. Convert text separately, then apply a date format if you want a readable display.

Why does YEAR give a result for a blank cell?
Excel can treat a blank reference like zero in date calculations, which may yield an early year rather than an empty result. If blank rows should remain blank, use an IF check around YEAR, and ensure nonblank inputs are valid dates.

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.