Article ID: 190831
Article Last Modified on 10/10/2006
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q190831
SYMPTOMS
In Microsoft Excel 97 for Windows, if you use the Evaluate method in a Visual Basic for Applications macro to evaluate a statement or formula that contains a date, the method may return either of the following error messages:
-or-
CAUSE
This problem may occur when the following conditions are true:
- The date you are passing to the Evaluate method contains a two-digit year instead of a four-digit year. For example, the date contains the year "25" rather than "2025". -and-
- The date you are passing to the Evaluate method uses either the day/month/year order or the year/month/day order. -and-
- The date is stored as a text string, for example, "25/7/5".
Specifically, the problem occurs because Microsoft Excel tries to evaluate the date using the month/day/year date order, even if the date was not entered using that date order. If the date includes a four-digit year, Microsoft Excel is able to determine the correct date; if not, the date may not be able to be resolved. If this is the case, Microsoft Excel returns an error value.
See the "More Information" section later in this article for a demonstration of this problem.
WORKAROUND
To prevent this problem from occurring, use any of the following methods:
- Use four-digit years whenever possible in your dates. For example, instead of typing "25/1/1" (January 1, 2025), type "2025/1/1". -or-
- Make sure that all of the dates are entered using the month/day/year date order. -or-
Use the DATE function to convert text dates into serial dates, for example:
Application.Evaluate("DATE(25,7,5)+1")
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
To reproduce this problem, follow these steps:
- Start Microsoft Excel 97. On the Tools menu, point to Macro, and click Visual Basic Editor.
- On the View menu, click Immediate Window. Or, press CTRL+G.
Type the following lines of code in the Immediate window. Press ENTER after you enter each line of code:
?Application.Evaluate("=""2025/7/5""+1") ?Application.Evaluate("=""7/5/25""+1") ?Application.Evaluate("=""25/7/5""+1")
Note the following:
- The first line of code returns the correct result 45844, the serial number value of July 5, 2025. Even though the date uses year/month/day date order, the result is correct because the date includes a four-digit year.
- The second line of code also returns 45844. The result is correct because the date was entered using month/day/year date order.
- However, the third line of code returns Error 2015 because the date uses a two-digit year and the date is entered using year/month/day date order.
Also, if you run the following macro
Sub Test() MsgBox Application.Evaluate("=""25/7/5""+1") End Sub
you will receive a run-time error 13 (type mismatch) error message. The problem occurs because the date uses a two-digit year and is entered using year/month/day date order.
Additional query words: XL97 year2000
Keywords: kberrmsg kbprb KB190831