Microsoft KB Archive/190831

= XL97: Error 2015 or Type Mismatch Using Evaluate with Dates =

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:

Run-time error '13':

Type mismatch

-or-

Error 2015



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.</li> On the View menu, click Immediate Window. Or, press CTRL+G.</li>  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") </li></ol>

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

-

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

© Microsoft Corporation. All rights reserved.