Article ID: 180159
Article Last Modified on 8/9/2006
APPLIES TO
- 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
