Microsoft KB Archive/24972

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:52, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


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

Last reviewed: March 25, 1998
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:

  1. Select cell A1.
  2. From the Formula menu, choose Define Name.
  3. Select Command.
  4. 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:

  1. Select the first cell on the worksheet that contains dates to be converted.
  2. 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:

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

Excel subtracts 1462 from the selected cells, yielding the proper date. For more information about the 1900 and 1904 Date Systems, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q180162
   TITLE     : XL: The 1900 Date System vs. the 1904 Date System

Additional query words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00



Last reviewed: March 25, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.