KB180159 - Dates Inserted by Recorded Macro May Be in Wrong Century

Article ID: 180159

Article Last Modified on 8/9/2006

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

This article was previously published under Q180159

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 it.

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"

When you run this line of code, Microsoft Excel inserts the year digits in the cell as the two-digit number 30. As a result, the date falls between 1930 and 2029 regardless of the date you entered when you recorded the macro.

Microsoft Excel interprets 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:

302768 How Microsoft Excel works with two-digit year numbers

RESOLUTION

To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2). For versions of Excel other than Excel 97, see the "Workaround" section of this article.

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

151261 : OFF97: How to Obtain and Install MS Office 97 SR-2

WORKAROUND

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

   ActiveCell.FormulaR1C1 = "1/1/30"

change it to

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

or change it to

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

After you do this, the macro inserts the correct date in the active cell when you run the macro.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 Service Release 2 (SR-2).

Additional query words: XL97 XL98 y2k year2000 1919 1920 1929 1930 2019 2020 2029 2030 XL

Keywords: kbbug kbfix KB180159