Excel YEAR Formula Not Working? Fix Dates and Errors

If the YEAR formula is not working, first check whether the cell contains a real Excel date rather than text. With a valid date in A2, enter =YEAR(A2); it should return a four-digit year such as 2024. If the result is wrong, blank, or an error, the cause is usually the cell’s contents, date interpretation, or formatting—not the YEAR function itself.

What the YEAR function does

YEAR returns the year component of an Excel date as a number. Excel generally stores dates as serial numbers and displays those numbers in a date format. For example, a cell that looks like July 19, 2024 may contain a numeric date value underneath; YEAR reads that value and returns 2024. A date and a text string that merely looks like the same date are not necessarily equivalent to Excel.

The function has one argument: a serial number representing a date. The argument can be a cell reference or a date constructed with another function. YEAR does not calculate how many years have elapsed, and it does not return a year as date text. Those are different tasks. If all you need is the calendar year of a valid date, YEAR is the appropriate function.

For example, with the date July 19, 2024 in A2, =YEAR(A2) returns the number 2024. It returns a number rather than the text string 2024, which matters if you later sort, compare, or use the result in other calculations.

Check whether the cell contains a real date

A date can look correct on screen but be stored as text. This commonly happens when dates are imported from a website or CSV file, copied from another system, or entered in a format that does not match the computer’s regional settings. If YEAR returns #VALUE!, a text date is a likely explanation.

Select the source cell and look at the formula bar and the Number Format control on the Home tab. Changing the display format can make a numeric date look different, but formatting text as a date does not convert it into a real date. A useful check is to set a suspected date cell to General: a genuine date typically displays as a serial number, while text remains text. Alignment can provide a clue—numbers often align right and text often aligns left by default—but alignment alone is not proof.

When the source is text in a consistent, fixed YYYY-MM-DD pattern, such as 2024-07-19 in A2, you can extract its leading four characters. With that exact text layout in A2, use =VALUE(LEFT(A2,4)); it returns the number 2024. This is a text extraction, not a full date validation: it assumes the first four characters are a valid year and does not confirm that the rest of the string is a valid date.

If you need to convert text into a usable date, use a conversion method that matches the data’s format. DATEVALUE can interpret date text according to Excel’s regional settings, so it may not reliably interpret the same ambiguous text on every computer. For fixed-format data, import it with the correct date order or build a date from its year, month, and day components. Always check a few converted results before applying the conversion to a large column.

Fix text dates and regional-format mismatches

Excel may interpret 04/05/2024 as April 5 or May 4 depending on regional date conventions. If Excel has already interpreted an entry as a date, YEAR will return the year accurately—but the date itself may not be the one you intended. If it remains text, YEAR may return an error. Inspect the source data and confirm the intended order of day, month, and year before converting it.

For a column of consistently formatted imported dates, select the column and choose Data > Text to Columns. Follow the wizard and, where the data is a date, select the matching date order on the final step before finishing. This can convert text into Excel date values. Make a copy of the data first if the column is important, and verify examples from different parts of the column afterward. On Mac, the Text to Columns command is also on the Data tab, though the dialog layout can differ.

Another option is a formula that constructs a date from fixed-position parts of a text value. With text exactly in YYYY-MM-DD format in A2, use =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))). The result is an Excel date value, which you can format as a date and then pass to YEAR. This formula relies on the characters being in those exact positions; it is not suitable for variable-length formats such as 7/19/2024.

DATE also normalizes out-of-range month or day values rather than always rejecting them. For instance, an incorrectly extracted month can roll into another year. That is why conversion should be checked against the original text, especially when the source format is inconsistent.

Understand common errors and unexpected results

#VALUE!: The referenced cell may contain text Excel cannot interpret as a date, or another error value. Confirm the cell’s actual contents and convert text dates appropriately.
1900 for an apparently blank cell: In the 1900 date system, a truly blank referenced cell can be treated like zero in this calculation, and YEAR can return 1900. If blanks should remain blank, with dates in A2, use =IF(A2="","",YEAR(A2)). This returns an empty string for a blank cell and the year for a populated date.
A four-digit value that looks like a date: YEAR returns a number. If the result cell has a date format, Excel may display that number as a date near the beginning of its date system, making it appear to be the wrong year. Set the result cell to General or Number.
A year one too early or late: The source date may have been interpreted with the wrong month/day order, or the workbook may use a different date system. Check the underlying date, not just the YEAR result.
The formula appears as text: The cell may be formatted as Text, the entry may begin with an apostrophe, or formula display may be enabled. Change the cell format to General, then re-enter the formula. Merely changing the format may not make an existing text entry recalculate.

Check formula entry and calculation settings

Make sure the formula begins with an equals sign and references the intended cell. A typo in the cell reference can produce a valid but unrelated year. If your Excel installation uses a regional list separator other than a comma, Excel may require that separator in functions with multiple arguments. The single-argument YEAR example does not have that issue.

If the cell shows =YEAR(A2) instead of its result, first check whether the cell is formatted as Text. Change it to General, select the formula cell, and press F2 then Enter on Windows to re-enter it. The equivalent on Mac is Control+U to edit the cell, then Return; keyboard behavior may depend on the keyboard and Excel settings. Also check whether Show Formulas is enabled, since that setting displays formulas across the worksheet rather than their results.

If the formula is correct but a recently changed source date has not updated, calculation may be set to Manual. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. Excel for Mac also has calculation options on the Formulas tab, although the interface can vary by release. In Automatic mode, changing a referenced date should update the YEAR result without re-entering the formula.

Format the year result correctly

YEAR returns a number, so the result cell should normally use General or Number format. If the result is 2024 but displays as a date, select the cell and choose Home > Number Format > General. This changes how the result is displayed; it does not change the source date or the calculation.

If you want to display only the year while retaining a full date value, use a date number format rather than replacing the date with a YEAR result. Select the date cell, open Format Cells, choose Custom, and enter yyyy as the format. The cell continues to contain the complete date, so formulas can still use its month and day. By contrast, YEAR produces just the numeric year, which is useful for grouping or comparisons.

For a text label rather than a numeric year, with a valid date in A2, use =TEXT(A2,"yyyy"). This returns text such as 2024. Text can be convenient for labels, but it is less suitable than a number when you need numeric comparisons or calculations. If the result will be used in further formulas, prefer YEAR unless there is a specific reason to produce text.

Date systems and dates outside the usual range

Most current Windows workbooks use the 1900 date system. Some workbooks, particularly older ones created on Mac, may use the 1904 date system. The same serial number represents dates 1,462 days apart in these systems. If dates appear shifted after copying between workbooks, check the date-system setting before changing the YEAR formula. On Windows, find it under File > Options > Advanced, in the section labeled When calculating this workbook. Changing the setting can affect displayed dates throughout the workbook, so do not switch it casually.

Excel’s 1900 date system also preserves a historical compatibility quirk: it treats 1900 as if it were a leap year, even though it was not. This can affect calculations around the very beginning of that date system, but it is not a likely explanation for a wrong year in ordinary modern dates. Standard Excel date values also do not handle dates before 1900 in the same straightforward way as modern dates; specialized historical-date work may need a different representation.

Choose YEAR or another approach

Use YEAR when you need the year as a number from a valid date. Use a custom yyyy display format when you want to show only the year but preserve the complete date in the cell. Use TEXT with a yyyy format when a text result is specifically required for a label or concatenated output. These approaches can look similar on the worksheet, but their underlying values differ.

For a date in A2, =YEAR(A2) returns numeric 2024, while =TEXT(A2,"yyyy") returns text 2024. Applying the custom format yyyy to A2 still leaves the original date value in A2. Choosing the right approach avoids problems later—for example, text years may sort differently from numbers if the data contains inconsistent text.

Frequently Asked Questions

Why does YEAR return #VALUE!?
Excel may be reading the referenced value as text rather than as a date, or the cell may contain another error. Check the source format and convert the value into a genuine Excel date before using YEAR.

Why does YEAR return 1900 for a blank cell?
A blank reference can be treated as zero in the 1900 date system, and zero corresponds to the start of that system for this kind of calculation. To keep blank rows blank, use the IF formula shown above and make sure it references the correct source cell.

Why does my YEAR result show a date instead of a year?
The formula returns a number, but the result cell may be formatted as a date. Change the result format to General or Number to display the year as 2024 rather than as a date derived from the number 2024.

Can YEAR extract a year from text?
It can work when Excel recognizes the text as a date, but that interpretation can depend on regional settings. For a fixed YYYY-MM-DD string, extracting the first four characters with VALUE and LEFT returns its year, though that method does not validate the entire date.

Does YEAR include the current year automatically?
No. YEAR returns the year of the date you give it. If the referenced date changes, the result updates under automatic calculation; YEAR does not replace a blank or missing date with today’s date.

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.