How to Make Excel Interpret Month-and-Day Entries as Specific Year Dates
Question details
The user wants to understand how Excel assigns years to month-and-day entries and how to configure the 1900 and 1904 date systems to interpret dates correctly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering incomplete dates (only a month and day) into a spreadsheet and needing them to reflect a specific year.
- Observed behavior
- When entering only a month and day, Excel automatically assumes the current system year. Changing the date system setting after entering dates shifts existing values by four years and one day.
Before modifying the date system settings in your workbook, be aware that switching between the 1900 and 1904 date systems will shift all existing dates in the current file by four years and one day.
Switch to the Default 1900 Date System
Ensure Excel uses the standard 1900 date system to interpret dates correctly and prevent unwanted 4-year shifts in your workbook.
Excel uses two date systems: 1900 and 1904. The 1900 date system is the default for Windows. If your dates are inexplicably shifted by four years, checking or unchecking this setting usually resolves the problem.
Click on 'File' in the top-left corner of the ribbon, then select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on the 'Advanced' tab in the left pane.
Scroll down the right pane until you find the section labeled 'When calculating this workbook'.
Uncheck the box that says 'Use 1904 date system' (or check it if you intentionally need Mac compatibility). Click 'OK' to apply the changes.
Explicitly Format and Input the Desired Year
Prevent Excel from defaulting to the current system year by explicitly typing the year or adjusting cell formatting.
Handle Month-and-Day Entries Smoothly with WPS Office
WPS Spreadsheet fully supports both the 1900 and 1904 date systems and accurately processes month-and-day entries, offering a seamless and free alternative for all your data management needs.
- 1. Open Your File: Launch WPS Spreadsheet and open the workbook containing your dates.
- 2. Access Options: Click the 'Menu' button in the top-left corner and select 'Options' from the drop-down list.
- 3. Go to Calculation Settings: In the Options dialog, click on 'Calculation' in the left pane.
- 4. Manage Date Systems: Check or uncheck 'Use 1904 date system' under the Workbook options to correct shifted dates, then click 'OK'.
Frequently Asked Questions
Why did my dates suddenly shift backward by four years?
This happens if the 'Use 1904 date system' option is cleared after dates have already been entered into the file. Because spreadsheet programs store dates as serial numbers based on a starting epoch, changing the epoch from 1904 to 1900 shifts the visible date of existing values by exactly four years and one day.
How do I make Excel assume a different year than the current one for new entries?
Excel will always use your computer's current system year when you only enter a month and day. There is no setting to change this default assumption. To assign a different year, you must explicitly type the year into the cell (e.g., 8/18/25 instead of just 8/18).
Does changing the date system affect all my Excel files?
No. The 1900 or 1904 date system setting is saved on a per-workbook basis. Changing it in one file does not affect other existing workbooks, nor does it alter the default setting for new, blank workbooks.
