Dates Inserted by Recorded Macro May Be in Wrong Century

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.

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:



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  (http://support.microsoft.com/kb/164406/EN-US/ ) How Microsoft Excel works with two-digit year numbers

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  (http://support.microsoft.com/kb/151261/EN-US/ ) : OFF97: How to Obtain and Install MS Office 97 SR-2

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



change it to



or change it to



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

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