Why Is the YEAR Function Not Working in Excel? Fixes

The YEAR function usually fails because the referenced value is text or an invalid date, or because Excel is displaying the formula instead of calculating it. With a real Excel date in A2—for example, March 15, 2024—use =YEAR(A2); the result is 2024.

What the YEAR function does

YEAR extracts the year number from an Excel date. Its syntax is YEAR(serial_number), where the argument is a date value or a reference to a cell containing one. The result is a number, such as 2024, not a complete date. It does not change the original cell or determine how that cell is displayed.

Excel stores recognized dates as serial numbers and applies a date format to show them as calendar dates. That distinction explains many YEAR problems: a cell can look like a date while containing text, or it can contain a valid date whose display format makes it look unexpected. Formatting alone cannot turn text into a date value.

For a self-contained example with no worksheet cell layout required, =YEAR(DATE(2024,3,15)) returns 2024. DATE constructs a date from year, month, and day numbers, so this example does not depend on how a person typed a date or on regional date-entry conventions.

Check whether the source value is a real date

Start by selecting the cell passed to YEAR and inspecting its contents in the formula bar. A date imported from a website, CSV file, database, or another spreadsheet may be text even if it resembles a date. Common clues include a leading apostrophe, a warning indicator, or the value remaining unchanged when you apply a different date format. Alignment is only a clue, not a definitive test, because users can change alignment manually.

A simple diagnostic is to change the source cell’s number format to General. A real Excel date normally appears as a serial number; text remains text. Restore a date format afterward if needed. Do not use this check alone for unusual values or cells with custom formatting, but it can quickly reveal whether Excel is storing a date value.

If A2 contains the text date 3/15/2024 and the current Excel regional settings recognize that format, the following formula returns 2024. The assumed layout is the date text in A2; DATEVALUE converts recognized date text into an Excel date value before YEAR extracts its year:

=YEAR(DATEVALUE(A2))

DATEVALUE depends on the computer’s regional date interpretation. For example, 4/5/2024 can mean April 5 or May 4 in different regions. If Excel rejects the text or interprets it differently from what you intended, do not assume the result is correct. Use a clearly defined input format or convert the year, month, and day components explicitly.

For text stored consistently as YYYY-MM-DD, with that exact text in A2, the formula below builds a date from the separate components and returns 2024 for 2024-03-15:

=YEAR(DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))))

This approach assumes every value has the same four-digit-year, two-digit-month, two-digit-day structure. It is not a general parser for mixed formats, extra spaces, or dates with different separators. For large imports, correcting the source or using a deliberate data-cleaning process is usually safer than applying a formula built for one format to every row.

Understand common errors and wrong results

#VALUE!: The argument may be text that Excel cannot interpret as a date, or another unsuitable value. Check the source cell and its regional date format.
#NAME?: Check for a misspelled function name, missing equals sign, or incorrectly typed formula. The function name in an English-language Excel installation is YEAR.
A year that is off by one: The source may have been interpreted using the wrong month/day order, or an imported date may have been shifted. Inspect the actual date value, not just the displayed year.
1900 or another surprising year: A blank or zero-like input can behave like a date serial near the start of Excel’s date system. Check for empty cells, formulas returning zero, and invalid source data.
A formula displayed literally: The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled.

A common misconception is that changing a text value’s number format to Date will convert it. Number formatting changes how numeric values appear; it does not reliably parse text into a date. Convert the text first, then apply the display format you want.

Fix formulas shown as text or not recalculating

If you see the characters =YEAR(A2) in the cell instead of a result, first check whether the cell is formatted as Text. Change the cell format to General, then edit and re-enter the formula so Excel evaluates it. Merely switching the format may not make an existing text entry recalculate. Also remove any apostrophe immediately before the equals sign.

If many cells show formulas rather than results, Show Formulas may be turned on. On Windows, use the Formulas tab and select Show Formulas to toggle the display; the same command is available on the Formulas tab in Excel for Mac. When this view is active, Excel displays formulas across the worksheet without changing their underlying calculations.

If the formula is correct but its result stays old after the source changes, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. Excel for Mac also provides calculation options on the Formulas tab. In Manual mode, formulas may not update until calculation is triggered; switching to Automatic is appropriate for most ordinary workbooks, though large models may intentionally use manual calculation.

Handle blank cells and incomplete data

A blank source cell can produce a result that looks like a real year because Excel may treat a blank reference like a zero date serial in this context. If a column has not been filled in yet, prevent the result from appearing until the source cell contains something. With the source date in A2, this formula returns a blank for an empty A2 and returns the year when A2 contains a date:

=IF(A2=",",YEAR(A2))

The formula above must use an empty text value in the first IF argument. Its correctly written form is =IF(A2="","",YEAR(A2)). With A2 empty, the result is blank; with a valid date of March 15, 2024 in A2, the result is 2024. This check is useful for genuinely empty cells and formulas that return an empty string, but it does not validate every possible text value. An invalid nonblank date can still cause an error.

In a data table, consider adding a separate validation step rather than hiding every error. A blank output can make a report cleaner, but suppressing errors indiscriminately can conceal bad imports or inconsistent date formats that should be corrected.

Check date systems and workbook compatibility

Excel workbooks can use the 1900 or 1904 date system. They assign different serial numbers to the same calendar date, with a difference of 1,462 days. This is most likely to matter when dates are copied between workbooks configured with different systems: a date may shift by several years even though the copied cell still appears to contain a date.

On Windows, the setting is under File > Options > Advanced, in the section labeled When calculating this workbook, where Use 1904 date system can be checked or unchecked. Review the setting in both workbooks if copied dates seem shifted. Do not change it casually in an established workbook: changing the date system can alter the displayed dates of existing serial values.

The 1900 date system also preserves a historical compatibility quirk: Excel treats 1900 as a leap year for date-serial compatibility, although it was not a leap year in the Gregorian calendar. This rarely explains a modern date problem, but it can matter in calculations involving dates near the beginning of 1900. For routine current dates, incorrect text parsing, blank cells, or a workbook date-system mismatch are more likely causes.

Distinguish YEAR from date formatting and related functions

Use YEAR when you need the year as a number for a calculation, grouping, or condition. If you only want a date to look like a year while retaining the underlying date, apply a custom number format such as yyyy instead. The distinction matters: formatting a date as yyyy changes its appearance, while YEAR returns a separate numeric result that can be used in another formula.

For example, if A2 contains a real date, formatting A2 as yyyy can display only its year while preserving the date value. By contrast, using YEAR in another cell returns a year number. Avoid replacing a complete date with its year if later work needs the month or day.

YEAR is also different from extracting the last four characters of a displayed date. Character extraction depends on the text representation and can fail when the date format or regional settings change. If the source is a valid Excel date, YEAR is the appropriate function. If the source is text, convert it deliberately before extracting the year.

A practical troubleshooting sequence

Work through these checks in order; each one addresses a different cause and helps avoid changing a workbook setting unnecessarily:

Confirm that the formula is spelled =YEAR(reference) and that the reference points to the intended cell.
Inspect the source value. Determine whether it is a genuine Excel date, text that resembles one, blank, or a formula result.
If it is text, convert it using a method that matches the text’s known format. Verify the resulting calendar date before relying on its year.
If the formula appears literally, check the cell’s Text format, a leading apostrophe, and Show Formulas.
If the result does not update, check that calculation is set to Automatic.
If a date shifted after copying between files, compare the workbooks’ 1900/1904 date-system settings.

After each change, check a known date such as March 15, 2024 and confirm the expected year is 2024. Testing one known value can expose a parsing problem that would otherwise quietly affect an entire column.

Frequently Asked Questions

Why does YEAR return #VALUE!?

Most often, the referenced cell contains text that Excel cannot recognize as a date. Check its exact contents and regional date order, then convert it to a real date value before using YEAR.

Why does YEAR return 1900 for a blank cell?

A blank reference can be treated like a zero date serial, which corresponds to the beginning of Excel’s date system. If blanks should remain blank, use an IF check before extracting the year.

Does YEAR work on dates stored as text?

It works only when Excel can interpret the supplied value as a date in the current context. Imported or ambiguous text may return an error or be read incorrectly, so convert and verify it rather than relying on its appearance.

Why does YEAR show the formula instead of the year?

The formula cell may be formatted as Text, have a leading apostrophe, or be affected by Show Formulas. Set the cell to General and re-enter the formula, and turn off Show Formulas if the whole sheet displays formulas.

Can I show only the year without using YEAR?

Yes. If the cell contains a real date and you only want to change its appearance, apply the custom number format yyyy. Use YEAR when you need a numeric year result for calculations or other formulas.

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.