'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel for Web Date Picker Entering the Wrong Date
Question details
Users experience an issue where selecting a date using the built-in date picker in Excel for the Web inputs a date several years earlier than expected.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the date picker tool to insert dates into cells in an Excel for the Web workbook.
- Observed behavior
- The date picker inputs a date exactly four years and one day earlier than selected, while using the Ctrl+; shortcut enters the current date correctly.
Ensure you have access to a desktop spreadsheet application like Microsoft Excel or WPS Office, as advanced workbook calculation settings cannot be modified directly within Excel for the Web.
Disable the 1904 Date System in Excel Desktop
Use the desktop version of Microsoft Excel to access advanced workbook options and turn off the legacy 1904 date system causing the offset.
The issue occurs because the workbook is configured to use the 1904 date system. This was originally created for compatibility with early Macintosh computers, setting the starting date for calculations to January 1, 1904, instead of January 1, 1900. Excel for the Web's date picker conflicts with this setting.
From Excel for the Web, click 'Editing' in the ribbon and select 'Open in Desktop App', or manually download the file and open it using your desktop version of Microsoft Excel.
Click on the 'File' tab in the top-left corner of the Excel window, then select 'Options' at the very bottom of the left-hand menu.
In the Excel Options dialog box, click on the 'Advanced' category in the left pane.
Scroll down to the section labeled 'When calculating this workbook'. Uncheck the box next to 'Use 1904 date system' and click 'OK' to apply the changes.
Save the workbook and wait for it to sync (or upload it manually). Reopen the file in Excel for the Web and use the date picker to confirm the correct date is now entered.
Easily Fix the 1904 Date System with WPS Office
If you don't have Microsoft Excel Desktop installed, you can use WPS Spreadsheet to open your workbook and disable the 1904 date system. It provides full access to advanced workbook settings and seamlessly handles Excel files.
- 1. Install WPS Office: Download and install the free version of WPS Office on your computer.
- 2. Open the spreadsheet: Launch WPS Spreadsheet and open the affected workbook.
- 3. Access Options: Click on 'Menu' in the top-left corner and select 'Options' from the drop-down list.
- 4. Change Calculation Settings: Navigate to the 'Calculation' tab, uncheck the '1904 date system' box, and click 'OK'.
- 5. Save the file: Save your document and re-upload it to your web drive to use the date picker flawlessly.
Frequently Asked Questions
What is the 1904 date system in Excel?
The 1904 date system is an alternative date calculation method originally designed for compatibility with early Macintosh computers. It uses January 1, 1904, as the baseline for calculating dates, whereas the default Windows system uses January 1, 1900.
Why does the Ctrl+; shortcut work while the date picker fails?
The Ctrl+; keyboard shortcut pulls the current date directly from your system's operating system clock and inserts it as a fixed value. The web-based date picker widget, however, processes dates through the workbook's internal calculation settings, which creates an offset when the 1904 system is active.
Can I disable the 1904 date system directly in Excel for the Web?
No, Excel for the Web currently lacks the advanced Options menu required to toggle workbook calculation systems. You must open the file in a desktop application like Microsoft Excel or WPS Spreadsheet to change this setting.
