Lotus 1-2-3 Files and the 1904 Date System

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0
• Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

When (from Microsoft Excel for Windows or OS/2 or from Lotus 1-2-3) you open a previously saved Microsoft Excel file and find that all date entries are four years and one day less than they should be, the problem could have one of two causes:

1. The file originated from a version of Microsoft Excel for the Macintosh prior to Version 2.20. Prior versions use a date system that begins in 1904, rather than 1900 as used by Microsoft Excel for Windows, Microsoft Excel for OS/2, and Lotus 1-2-3.

   To correct the problem in Microsoft Excel, choose Options Calculation and check the 1904 Date System option. Lotus 1-2-3 does not have this option (see the recalculation procedure below).

2. You previously saved the file in Microsoft Excel in either the WK1 or the WKS format. These formats cause Excel to save the file's dates based on the standard translations of the serial values, not the 1904 Date System translations.

   To correct the problem, see item 1 above.

MORE INFORMATION

The problem most likely occurs when the file originated from a version of Microsoft Excel for the Macintosh prior to Version 2.20. All versions of Microsoft Excel use a serial time format and store dates as a single number. Microsoft Excel for the Macintosh (prior to Version 2.20) begins counting dates from the number 0, which corresponds to January 1, 1904. Microsoft Excel for Windows and Microsoft Excel for OS/2 begin counting dates from the number 1, which corresponds to January 1, 1900 --- hence the four-year-and-one-day difference between the Macintosh and IBM-compatible versions.

Microsoft Excel for Windows and Microsoft Excel for OS/2 have an option to correct the inconsistency between the programs when importing Macintosh files to the PC environment: the 1904 Date System option under Options Calculation. If this option is not selected when a file from Macintosh Excel is imported, then all dates will be four years and one day behind the dates entered when the file was used with Macintosh Excel. When the option is selected, Microsoft Excel for Windows and OS/2 add four years and one day from the standard translation it uses from the underlying serial value.

This option does not change the underlying serial value, however, and when Excel saves the file in either the WK1 or the WKS format, it saves the file's dates based on the standard translations of the serial values, not the 1904 Date System translations. Consequently, when the file is opened in Lotus 1-2-3, or reopened in Excel for Windows or OS/2, the dates are four years and one day behind the values typed into Excel. Although resetting the 1904 Date System option will correct the problem in Excel, Lotus 1-2-3 has no such feature.

For Lotus 1-2-3, the only way to correct the discrepancy (working in Excel before exporting to Lotus) is to add 1462 to the underlying serial value in each cell and disable the 1904 Date System option. (The number 1462 is equal to three years of 365 days each, plus one leap year of 366 days, plus an extra day to account for the fact that Macintosh Excel starts counting days at 0, whereas Excel for Windows starts counting at 1.)

For a large sequence of dates, this recalculation can be accomplished quickly by using the following method:

1. Type the number 1462 into a cell.

2. Select the cell.

3. Choose Edit Copy.

4. Select the range of dates to be converted.

5. Choose Edit Paste Special, activate the Paste Values and Operation Add options and choose OK (or press ENTER).

6. Repeat Steps 3-5, as necessary, for other date ranges.

7. Disable the 1904 Date System option under Options Calculation.

8. Save the file.