Formula Errors

Fix Excel YEAR Function Displaying 1900 Date Instead of Year

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to display a 4-digit year extracted from a date, but the YEAR function is outputting a full date in the early 1900s instead of just the year number.

Product: Excel
Device & OS: not provided
Scenario: Using the YEAR function to extract the year value from an existing date column.
Observed behavior: The formula returns a full date in the 1900s (e.g., 7/16/1905) instead of a standalone 4-digit year (e.g., 2023).

Before you start

Ensure you have selected the specific cells containing your YEAR function formula before attempting to adjust the formatting settings.

Solution 1Recommended

Change Cell Format to General or Number

Corrects the issue by instructing Excel to display the formula result as a standard number rather than interpreting it as a date serial value.

The YEAR function naturally returns a numeric value representing the year. However, if the destination cell is formatted as a Date, Excel interprets the output number (like 2023) as a date serial number. Since Excel's time system starts on January 1, 1900, a serial number of 2023 translates to the 2,023rd day after that date, resulting in a display date in mid-1905.

Select the Affected Cells

Click and drag to highlight the cell or range of cells where the YEAR function is displaying the incorrect 1900 date.

Open the Format Cells Menu

Right-click the highlighted cells and select 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.

Apply General or Number Format

Navigate to the 'Number' tab in the dialog box. Under the Category list, choose 'General'. Alternatively, you can choose 'Number' and set the 'Decimal places' to 0.

Confirm and Apply

Click the 'OK' button. The cells will instantly update to display the correct 4-digit year number.

Quick Formatting Tip: You can also quickly change the format by selecting the cells, navigating to the 'Home' tab on the ribbon, and choosing 'General' from the Number Format dropdown menu.

WPS Spreadsheet Solution

Extract Years and Format Cells Easily with WPS Spreadsheet

WPS Spreadsheet offers a highly intuitive interface for managing dates, formulas, and cell formatting. You can seamlessly extract years using the YEAR function and adjust formats without encountering persistent date serial errors.

1. Enter the YEAR formula: Type =YEAR(A1) into your target cell to extract the year from the date located in cell A1.
2. Select the result cell: Click on the cell that displays the formula result to make it the active cell.
3. Change format to General: Navigate to the Home tab and select 'General' from the Number Format dropdown menu to display the year properly.

100% compatible with Microsoft Excel formulas and formattingEasily change cell formats from Date to General with a single click in the Home ribbonLightweight application that processes large datasets quicklyFree to use with comprehensive spreadsheet functionalities

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel use 1900 as the base year for date calculations?

Excel calculates dates using a serial number system that begins on January 1, 1900 (serial number 1). When a cell formatted as a date receives a small number like 2024, it interprets it as the 2,024th day after January 1, 1900, which falls in the year 1905.

Can I prevent the YEAR function from automatically formatting as a date?

Yes. By default, if you enter a formula next to a date column, Excel may automatically inherit the formatting of the adjacent cell. To prevent this, pre-format your blank target column as 'General' before typing your YEAR function.

Is there an alternative function to extract the year without formatting issues?

Yes, you can use the TEXT function to extract the year as a text string instead of a number. By using the formula =TEXT(A1, "yyyy"), Excel returns the 4-digit year as text, which avoids the date serial number formatting issue completely.

Will changing the cell format alter my actual data?

No, changing a cell's format from Date to General only alters how the data is visually displayed on your screen. The underlying numeric value (the calculated year number) remains completely unchanged.

Excel & Data Problems