Fix Excel Dates That Change When Worksheets Are Copied in WPS Office
Question details
The user needs to stop dates from shifting by several years when a worksheet is moved or copied between Excel workbooks that use different date systems.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet is copied or moved from one workbook to another, and the destination workbook uses a different date system from the source.
- Observed behavior
- Existing dates change to different years after the worksheet is copied because the 1900 and 1904 date systems assign different calendar dates to the same serial numbers.
Open the destination workbook in WPS Office Spreadsheets and check whether the dates are off by roughly four years. This is the typical sign of a 1900/1904 date system mismatch rather than a cell formatting issue.
Switch the Destination Workbook to the 1900 Date System in WPS Office Spreadsheets
Use this when copied worksheet dates appear four years earlier or later; aligning the date systems prevents the shift for all dates at once.
Excel-compatible workbooks can use either the 1900 date system or the 1904 date system. When source and destination workbooks use different systems, serial date values point to different calendar dates after the worksheet is copied.
In WPS Office Spreadsheets, open the workbook where the dates changed after copying the worksheet.
Click the Menu button in the top-left corner, then choose Options.
In the Options dialog, open the Calculation tab and locate the Use 1904 date system option.
Clear Use 1904 date system so the workbook uses the 1900 date system, then click OK.
Save the destination workbook, then copy or move the worksheet again and verify that the dates no longer shift.
Manually Adjust Shifted Dates with Paste Special
Use this only for a small number of affected dates when you cannot change the workbook date system.
Fix Copied Worksheet Dates in WPS Office Spreadsheets
Open the destination workbook in WPS Office Spreadsheets, switch the workbook date system to match the source, and save the file so dates stop shifting by four years after copying worksheets.
- 1. Open both workbooks: Open the source and destination workbooks in WPS Office Spreadsheets.
- 2. Open Options in the destination workbook: Click the Menu button in the top-left corner of the destination workbook and choose Options.
- 3. Change the date system: In the Options dialog, open the Calculation tab and clear Use 1904 date system, or set it to match the source workbook.
- 4. Save the destination workbook: Click OK, then save the workbook before copying the worksheet again.
- 5. Copy and verify: Copy or move the worksheet into the destination workbook and confirm that the dates remain correct.
Frequently Asked Questions
Why do Excel dates shift by several years when I copy a worksheet?
This happens when the source and destination workbooks use different date systems. Excel-compatible applications support both the 1900 and 1904 date systems, and the same serial date value points to different calendar dates in each system.
Where is the Use 1904 date system setting in WPS Office Spreadsheets?
Open the destination workbook, click the Menu button in the top-left corner, choose Options, and open the Calculation tab. Then clear Use 1904 date system to switch to the 1900 date system.
Can I fix shifted dates without changing the date system?
Yes. For a small number of affected cells, you can add or subtract 1,462 days with Paste Special to manually realign the displayed dates.
Does this issue affect .xlsx files created in Microsoft Excel?
Yes. WPS Office Spreadsheets preserves the date system stored in .xlsx and .xls files, so workbooks created on Windows and older Mac versions may show this date shift when worksheets are copied.
Will clearing Use 1904 date system change existing dates?
Existing serial date values will be interpreted with the 1900 date system after the change, which may recalculate displayed dates. Save the workbook only after confirming the dates are correct.
