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

ID: Q180159

The information in this article applies to:

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

Last Reviewed: April 13, 1999