Formatting Issues

Fix Excel YEAR Formula Returns 1905 Instead of Correct Year

Olivia Miller Oct 9, 2026 869 views

Question details

The user needs to extract a year using a formula, but the result incorrectly displays as a date from the year 1905.

How to Fix Excel YEAR Formula Returning 1905

Product: Excel
Device & OS: not provided
Scenario: Extracting the year from a given date column using the =YEAR() formula.
Observed behavior: Excel displays a date in the year 1905 (e.g., 7/16/1905) instead of the expected integer year (e.g., 2024) because the cell retains a date format.

Before you start

Ensure you have selected the specific column or cells containing the YEAR formula before attempting to adjust the number formats.

Solution 1Recommended

Change Cell Format to General or Number

Modify the cell's number format so Excel treats the extracted year as a standard integer rather than a date serial number.

When you use the YEAR formula (e.g., =YEAR(A2)), Excel extracts the year and returns a numeric value like 2024. If the destination cell is formatted as a 'Date', Excel treats the number 2024 as the 2,024th day after January 1, 1900. This translates to a date in July 1905.

Select the formula column

Click and drag to highlight the cells, or click the column header containing your YEAR formula results.

Open the Home tab

Navigate to the 'Home' tab on the main Excel ribbon at the top of your screen.

Locate Number Format options

Find the 'Number' group in the toolbar. You will see a dropdown menu that likely currently says 'Date'.

Apply General format

Click the dropdown menu and select 'General' (or 'Number'). Your data will immediately change from the 1905 date to the correct year.

Format Applied: The cells will instantly update to display the correct four-digit year, such as 2024.

WPS Spreadsheet Alternative

Easily Extract and Format Dates with WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet tool that makes extracting years and formatting cells straightforward. Enjoy seamless formula execution without formatting headaches.

1. Open your file: Launch WPS Spreadsheet and open your workbook containing the dates.
2. Apply the YEAR formula: Type the =YEAR() formula pointing to your date cell and press Enter.
3. Format the cell: Right-click the result cell, select 'Format Cells', and choose 'General' under the Number tab to display the correct year.

Fully compatible with Microsoft Excel formats (.xlsx, .xls)Extract dates accurately with YEAR, MONTH, and DAY functionsIntuitive formatting tools to fix date serial number issues instantlyFree, lightweight, and fast office suite

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel turn the number 2024 into a 1905 date?

Excel stores dates as sequential serial numbers starting from January 1, 1900. The number 2024 represents the 2,024th day from that starting point, which happens to fall in the year 1905. When a cell is formatted as a Date, Excel displays that serial number as a calendar date.

Does this formatting issue happen with MONTH or DAY formulas?

Yes, if you use MONTH or DAY formulas and the result cell is formatted as a date, Excel will treat the resulting small numbers (like 1 to 31) as the very first days of January 1900.

How can I extract the year without dealing with number formatting?

Instead of using the YEAR formula, you can use the TEXT function to output the year directly as text. For example, typing =TEXT(A2, "yyyy") will extract the year and format it as text, bypassing the 1905 date serial number issue entirely.

Document & File Issues