XL: Dates Inserted by Recorded Macro May Be in Wrong Century

• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you enter a date in a cell while recording a Microsoft Visual Basic for Applications macro, the macro may enter the wrong date when you run the macro.

Specifically, the date may be in the wrong century; for example, instead of entering 1/1/2030 or 1/1/2130 the macro enters 1/1/1930.

CAUSE

This problem occurs when the recorded code contains a two-digit year number instead of a four-digit year number as in the following example:

   ActiveCell.FormulaR1C1 = "1/1/30"

Microsoft Excel 97 and Microsoft Excel 98 interpret two-digit years from 00 through 29 as 2000 through 2029. Therefore, the two-digit year 30 is treated as the year 1930.

For more information about how Microsoft Excel works with two-digit year numbers, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q164406
   TITLE     : XL: How Microsoft Excel Works with Two-Digit Year Numbers

WORKAROUND

To work around this problem, modify the recorded code. For example, if the line of code is the following

   ActiveCell.FormulaR1C1 = "1/1/30"

   ActiveCell.FormulaR1C1 = "1/1/2030"   ' January 1, 2030

   ActiveCell.FormulaR1C1 = "1/1/2130"   ' January 1, 2130

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.