XL: Dates Inserted by Recorded Macro May Be in Wrong Century

ID: Q180159

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a

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 into 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:

Q164406 : XL: 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, refer to the "Workaround" section of this article.

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

Q151261 : 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 into 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).

MORE INFORMATION

For more information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site:

http://www.microsoft.com/technet/topics/year2k/default.htm

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

Keywords : kbdta xlformula KbVBA
Version : MACINTOSH:5.0,98; WINDOWS:5.0,5.0a,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbbug