XL97: Error 2015 or Type Mismatch Using Evaluate with Dates (190831)

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")
```

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