Microsoft KB Archive/180159

= Dates Inserted by Recorded Macro May Be in Wrong Century =

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.