Dates Inserted by Recorded Macro May Be in Wrong Century (180159)

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