Formula Errors

How to Fix Excel YEAR Function Returning a Date in 1900

John Wilson Sep 28, 2026 869 views

Question details

The user needs to correct the output format of a cell containing the YEAR function so it displays the four-digit year number instead of an unexpected date in 1900.

How to Fix Excel YEAR Function Returning a Date in 1900

Product: Excel
Device & OS: not provided
Scenario: Extracting the year from a specific date using the YEAR formula.
Observed behavior: The formula result cell interprets the extracted year number as a date serial value and incorrectly displays a date in the year 1900.

Before you start

Check if the cell where you entered the YEAR formula was previously used to store dates, as Excel often carries over the 'Date' formatting to new formulas automatically.

Solution 1Recommended

Change the Cell Format to General or Number

Reformatting the result cell tells Excel to display the raw number returned by the YEAR function instead of converting it into a date serial value.

The YEAR function is designed to return a raw integer representing the year (such as 2024). However, if the result cell is formatted as a Date, Excel interprets that number as a date serial number. In Excel's timekeeping system, serial numbers in the 2000s equate to dates in the year 1905, and smaller numbers land in 1900. Reverting the cell format to General or Number resolves this immediately.

Select the target cell

Click on the cell or highlight the range of cells containing your YEAR function formula.

Open the Number Format menu

Navigate to the 'Home' tab on the Excel ribbon. Locate the 'Number' group and click on the formatting drop-down menu, which is likely displaying 'Date' or 'Custom'.

Apply General formatting

Select 'General' from the drop-down list. Alternatively, select 'Number' and use the 'Decrease Decimal' button to ensure there are zero decimal places.

Format Updated: The cell will instantly update to display the correct four-digit year as a standard number.

Solution 2

Use the TEXT Function as an Alternative

If you are combining the year with other text or want to permanently prevent date formatting issues, you can use the TEXT function to extract the year as a text string.

Efficient Spreadsheet Tool

Extract and Format Dates Effortlessly with WPS Spreadsheet

WPS Spreadsheet handles date formulas like YEAR seamlessly and allows you to fix formatting issues in just a few clicks. It offers full support for Microsoft Excel functions and an intuitive interface for managing your data.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your dates.
2. Enter the formula: Type =YEAR(A1) into your target cell to extract the year.
3. Right-click to format: If the result shows a 1900 date, right-click the cell and select 'Format Cells'.
4. Apply General format: Under the Number tab, select 'General' and click OK to display the correct year.

Seamless compatibility with Microsoft Excel .xlsx files and date formulasEasily change cell number formats using an intuitive Home ribbonLightweight application that runs smoothly without laggingFree to use for everyday spreadsheet tasks and data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel use the year 1900 for these date errors?

Excel stores dates as sequential serial numbers starting from January 1, 1900, which equals the serial number 1. When a formula returns a number like 2024 and the cell is formatted as a Date, Excel counts 2024 days from January 1, 1900, resulting in a date in July 1905. Smaller extracted numbers will result in a date directly in 1900.

Does this formatting issue happen with the MONTH or DAY functions?

Yes. The MONTH and DAY functions also return standard integers (e.g., 1 to 12 for months). If the formula result cell happens to be formatted as a Date, Excel will read those small integers as serial numbers, resulting in a date display within the first two weeks of January 1900.

How do I quickly clear all formatting from a cell to fix this?

You can easily reset the cell formatting by selecting the affected cell, navigating to the Home tab, locating the Editing group, clicking on the 'Clear' drop-down (often represented by an eraser icon), and choosing 'Clear Formats'.

Excel & Data Problems