Excel: Dates Imported from DOS Off by Four Years and One Day

Last reviewed: November 2, 1994
Article ID: Q24972

SUMMARY

Dates 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 INFORMATION

To 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:

Select cell A1.
From the Formula menu, choose Define Name.
Select Command.
Type a letter in the COMMAND+OPTION box to be used as the key command for the macro.

To run the macro, do the following:

Select the first cell on the worksheet that contains dates to be converted.
Hold down the COMMAND+OPTION keys and press the key that was defined for the macro in the Define Name dialog box.

The above commands proceed down the column of dates row by row until a blank cell is reached. These commands can be modified to handle other date arrangements.

Alternatively, you can use Copy/Paste Special to subtract 1462 from a range of cells by doing the following:

Enter 1462 into a blank cell.
Select the cell containing 1462.
From the Edit menu, choose Copy.
Select the range of dates to be converted.
From the Edit menu, choose Paste Special.
Select the Values and Subtract radio buttons.
Select OK.

Excel subtracts 1462 from the selected cells, yielding the proper date.

KBCategory: kbother
KBSubcategory:
Additional reference words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.