In our last challenge, Why does Excel change dates when I copy a sheet to a new workbook, I presented a rare date problem. Sometimes, when you copy a sheet to a new workbook, Excel changes the date. It isn’t a bug; you’re dealing with two different date systems.
By default, Excel workbooks use the 1900 date system. The first supported day is January 1, 1900. When you enter a date value, Excel converts that date into a serial number that represents the number of elapsed days since January 1, 1900. It’s my understanding that this system was originally adopted to be compatible with Lotus 1-2-3.
In contrast, the first day supported in the 1904 system is January 1, 1904. When you enter a date, Excel converts it into a serial number that represents the number of elapsed days since January 1, 1904. This gets into the leap year issue that TechRepublic member Paul mentioned.
The difference between the two systems, and consequently, their serial numbers is 1,462 days. 1900 serial numbers are always 1,462 days larger than the 1904 system’s.
Each workbook can support either date (but not both at the same date). To set the system, do the following:
- Click the File tab and choose Options. In Excel 2007, click the Office button, and click Excel Options. In Excel 2003, choose Options from the Tools menu.
- In the left pane, choose Advanced. In Excel 2003, click the Calculation tab.
- In the When Calculating This Workbook section, check the Use 1904 Date option, to change this setting. In Excel 2003, click the 1904 Date System option.
- Click OK.
When you copy data between two workbooks that use different systems, you’ll run into shifting dates. The easiest way to adjust the dates is to use the Paste Special option to add or subtract 1,462 to each date:
- Add 1,462 to shift dates up four years and one day.
- Subtract 1,462 to shift dates down four years and one day.
In my experience, these mysterious shifting dates seem to show up when working with legacy applications. It’s seldom an issue with new ones. Congratulations to Tron16 for being the first to identify the problem and thanks for another great challenge.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays