Excel: Dates Incorrect When Manipulated with Macros

 Last reviewed: November 30, 1994
Article ID: Q79582
Summary:

Macros that operate on worksheet dates give incorrect results or change worksheet dates by four years and one day if the worksheet and macro sheet are using different date systems.

To make sure the dates display correctly upon running the macro, make sure that the worksheet and the macro sheet have the same date system selected by choosing the Calculation command from the Options menu.

This information applies to Microsoft Excel for the Macintosh versions 2.2 and 3.0; Excel for Windows versions 2.1 and 3.0; and Excel for OS/2 versions 2.2, 2.21, and 3.0.

More Information:

The default date system for the Macintosh is 1904. The 1900 date system (1904 not selected) is available for compatibility with Windows and OS/2.

The following is an example of a macro formula that may cause dates to appear incorrectly: 

  =FORMULA(NOW(),!A1)
Normally, this would place today's date in cell A1 on the active worksheet. If the macro sheet is using a different date system, it will return a date that is off by four years and one day.

The same problem will occur if dates are copied and pasted from worksheets that are using different date systems.

Reference(s):

"Microsoft Excel User's Guide," version 3.0, page 291

KBCategory: kbother
KBSubcategory:

Additional reference words: 3.00 W_eXceL

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 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.