Excel: Dates Imported from DOS Off by Four Years and One DayLast reviewed: November 2, 1994Article ID: Q24972 |
SUMMARYDates in Microsoft Excel for the Macintosh are based on Macintosh System serial-number dates. A serial number of 0 on the Macintosh represents January 1, 1904. On DOS machines, the beginning serial number is 1, which represents January 1, 1900. Programs such as Lotus 1-2-3, Symphony, and Microsoft Excel for Windows generally use this numbering scheme; therefore, transferring worksheets between the two environments may cause dates to be altered. To convert DOS-based dates in Microsoft Excel for Windows to their equivalents in Microsoft Excel version 3.00 or 2.20 for the Macintosh, open the converted file in Excel for the Macintosh and choose Calculation from the Options menu and deselect the 1904 Date System. To convert DOS-based dates in Excel for Windows to Excel versions 1.50 and earlier for the Macintosh, subtract 1462 (the DOS serial number for January 1, 1904) from the dates in the converted file. (For more detailed information on how to accomplish this, see the "More Information" section below.) Another alternative for converting dates from Excel for Windows to Excel versions 1.50 and earlier for the Macintosh is to open the Excel for Windows file to be converted and choose Calculation from the Options menu and select the 1904 Date System. When you save the Excel for Windows file in the SYLK file format (which is required prior to transferring it to Excel versions 1.50 and earlier for the Macintosh), the date system information is also saved.
MORE INFORMATIONTo automate the conversion of 1-2-3 or Symphony dates to Excel 1.50 and earlier, enter the following macro commands into a macro sheet:
+---+------------------------------------+ | | A | +---+------------------------------------+ | 1 | FixDate | +---+------------------------------------+ | 2 | =IF(ACTIVE.CELL()="",HALT()) | +---+------------------------------------+ | 3 | =FORMULA(ACTIVE.CELL()-1462) | +---+------------------------------------+ | 4 | =SELECT(OFFSET(ACTIVE.CELL(),1,0)) | +---+------------------------------------+ | 5 | =GOTO(A2) | +---+------------------------------------+To define the above commands as a macro:
Alternatively, you can use Copy/Paste Special to subtract 1462 from a range of cells by doing the following:
|
KBCategory: kbother
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |