Excel YEAR Function Not Working? Fix Dates and Results

If Excel’s year-today formula is not working, enter =YEAR(TODAY()) in a cell formatted as General or Number; it should return the current year according to your computer’s date. If it shows an old year, an unexpected date, or the formula itself, check calculation settings, cell formatting, and whether the formula was entered as text.

What the year-today formula does

TODAY() returns the current date from the computer’s system clock, and YEAR() extracts the year from that date. Together, they provide a numeric year that updates when Excel recalculates the workbook. For example, on November 2, 2026, the result is 2026. The formula does not include a fixed date, so it can keep working in later years without being edited.

For the basic formula, assume you are entering it in an empty cell such as B2 and want the year for today; =YEAR(TODAY()) returns 2026 when the computer date is November 2, 2026. If Excel is set to a different date or has not recalculated, its displayed result may differ.

This is different from entering a year as a constant, such as 2026. A constant never changes on its own. It is also different from extracting the year from a date already in a worksheet. In that case, the formula refers to the cell containing that date, rather than to TODAY().

Check the formula and calculation settings first

Start by selecting the result cell and looking at the formula bar. Confirm that the entry begins with an equals sign, has the parentheses in the right places, and uses TODAY with no arguments. A common typing mistake is to enter =YEAR(TODAY) without the parentheses after TODAY; Excel needs the function call shown in the working formula.

Next, check whether the workbook is set to calculate automatically. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. If calculation is set to Manual, formulas can continue showing results from an earlier calculation. Press F9 to recalculate open workbooks. For a more thorough recalculation of formulas in the workbook, press Ctrl+Alt+F9.

Calculation mode is a workbook-level concern that can affect other formulas too. If a workbook was opened after working with a file that uses manual calculation, do not assume that a stale answer means YEAR or TODAY is broken. Check the setting, switch to Automatic if appropriate, and recalculate. F9 is the usual recalculation shortcut on Windows; Mac keyboard shortcuts can depend on the keyboard and system settings, so the Formulas tab is a reliable place to find calculation commands.

Check the computer’s date and time. TODAY follows the system date, not a date typed elsewhere in the workbook.
Check the formula bar to make sure the cell contains a formula rather than a typed year.
Set calculation to Automatic, then recalculate and see whether the result changes.

When Excel displays the formula instead of the answer

If the cell visibly contains =YEAR(TODAY()) rather than a year, the formula may be treated as text. This often happens when the cell was formatted as Text before the formula was entered, or when an apostrophe was placed before the equals sign. Select the cell and inspect the formula bar: a leading apostrophe or a Text number format can explain why Excel is not evaluating the entry.

To correct a Text-formatted cell, select it, choose Home > Number Format > General, then activate the cell for editing and press Enter. Simply changing the format may not convert an existing text string into a formula; re-entering it after changing the format prompts Excel to evaluate it. Remove an accidental leading apostrophe as well.

Another possibility is that Show Formulas is turned on. In Excel for Windows, use Formulas > Formula Auditing > Show Formulas to toggle the worksheet back to displaying calculated results. This setting changes what the sheet displays; it does not mean the formula is invalid. If other formula cells also show their formulas, this is a particularly likely explanation.

Why a year formula can display a strange date

YEAR(TODAY()) returns a number, such as 2026. If the result cell is formatted as a date, Excel may interpret that number as a date serial and display an unexpected date from the early 1900s instead of the year. The calculation may be correct even though the display is misleading.

Select the result cell and choose Home > Number Format > General or Number to show the numeric year. If your goal is to show a date in a particular style, format the date itself as a date; YEAR is meant to return just the year number. You can also open Format Cells with Ctrl+1 on Windows and select a suitable category or custom format.

Be clear about whether you need a number or a date display. A numeric year is useful for comparisons, sorting, and calculations. A date format is useful for a complete date. A custom display format such as yyyy can make a date cell show only its year, but it does not change the underlying date value. That distinction matters when using the cell in later calculations.

Using YEAR with a date in another cell

If the task is to return the year of a date in the worksheet, reference that date cell instead of using TODAY. Assume A2 contains the real Excel date July 4, 2024, and you enter the formula in B2; =YEAR(A2) returns 2024. If A2 is blank, YEAR can treat the blank reference like zero and produce a result that looks like a date-related error, so check that the source cell actually contains a date.

When a column can contain empty rows, it is often clearer to leave the result cell empty until a date is supplied. You can do that with an IF test around the YEAR calculation, but make sure the test matches how your source data represents missing values. A cell that looks blank might contain a formula returning an empty string, a space, or a zero; these are not all identical to a truly empty cell.

Also distinguish the year number from a date formatted to show a year. If a source date is July 4, 2024, the YEAR result is the number 2024. Formatting the original date cell to display only yyyy hides its month and day but keeps the complete date underneath. Use the extracted number when you need to group or compare records by year.

When the source date is stored as text

YEAR works with Excel date values, which Excel stores as serial numbers, not with every string that merely looks like a date. Data imported from a website, CSV file, database, or another application may contain text such as 2024-07-04. Depending on the text and regional settings, Excel may recognize it as a date or leave it as text. A text value can lead to an error when passed to YEAR.

Click the suspected date cell and inspect its alignment and formula bar, but do not rely on appearance alone: formatting can make text and date values look similar. One practical conversion option is Data > Text to Columns. Follow the wizard and, when selecting a date format, choose the order that matches the source data. For example, day-month-year and month-day-year are easy to confuse; choosing the wrong order can silently create the wrong date.

For a small number of records, re-entering a date with an unambiguous year, month, and day is another safe approach. Verify the result by formatting the cell as a full date and checking that the intended day and month appear. Do not convert a whole column blindly if its values mix formats or contain ambiguous dates. Keep a copy of the original imported data until the converted values have been checked.

Other causes: workbook dates and formula entry

Excel workbooks can use either the 1900 or 1904 date system. This setting affects how date serial numbers map to calendar dates, and can matter when dates are transferred between workbooks. It is not a normal fix for a YEAR(TODAY()) result that is one year out. Avoid changing the setting as a troubleshooting experiment: changing a workbook’s date system can shift existing dates by 1,462 days.

In Excel for Microsoft 365 on Windows, the workbook option is under File > Options > Advanced, in the section labeled “When calculating this workbook.” The date-system setting is mainly relevant when a workbook’s dates look shifted after being copied or linked from another file. If the computer’s current date is correct and TODAY returns a different calendar date, investigate the workbook and calculation context rather than changing this option casually.

Check for a misspelled function name, missing parentheses, or an extra character before the equals sign. Also make sure the formula has not been entered into a cell with a leading apostrophe. If Excel returns an error rather than a year, read the exact error and inspect the source value; a formula pointing to text or an invalid date is a different problem from a stale TODAY result.

Choosing between TODAY, YEAR, and date formatting

Use =YEAR(TODAY()) when you need a numeric value for the current year, such as a heading, a comparison, or a formula criterion. Use YEAR with a cell reference when you need the year attached to a recorded date. Use date formatting when you want a date cell to look like it contains only a year but still retain its complete date value.

TODAY updates when Excel recalculates; it is not a live clock that changes the cell at midnight while the workbook sits untouched. Reopening the workbook or triggering calculation normally refreshes it, provided calculation is enabled and the computer’s date is correct. This also means the formula is not suitable for preserving the year in which a transaction happened: use the transaction’s stored date for that purpose, not TODAY.

For a report intended to remain a permanent record, decide whether the year should update. A formula is appropriate for a live dashboard or current-year label. A fixed date or year is more suitable for a closed report whose displayed period must not change later. If you copy a formula result and paste values, the pasted year no longer updates automatically.

Frequently Asked Questions

Why does YEAR(TODAY()) show last year?
Check the computer’s system date first, then choose Formulas > Calculation Options > Automatic and recalculate. A workbook left in Manual calculation mode can show a result from an earlier date.

Why does Excel show a date instead of the year?
The result cell is probably formatted as a date. Change its number format to General or Number; YEAR returns a number, not a full calendar date.

Why is the formula visible in the cell?
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 and re-enter the formula, and check the Show Formulas setting.

Does TODAY update automatically at midnight?
TODAY returns the computer’s current date when Excel recalculates. It is not a continuously running clock, so an open workbook may need recalculation before the displayed date changes.

Can YEAR return the year from a date that looks correct?
Yes, if the cell contains a genuine Excel date value. If the apparent date is text from an import, convert it carefully and verify the day-month order before relying on the result.

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.