Microsoft KB Archive/72017

{|
 * width="100%"|

Incorrect Results Using Dates in Comparisons

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

-

SUMMARY
When comparing dates, either the serial number for the date or the DateValue function must be used.

Example
A1: 2/15/91 B1: =IF(A1=2/15/91,TRUE,FALSE)

If you type the above entries into a worksheet, B1 will return FALSE.

Excel treats the date in the IF statement as a calculation and actually divides the numbers. For example, 2/15/91=.001465 is the result of dividing 2 by 15 and dividing the result by 91.

The DateValue function returns the serial number of a given date (in text form). Applying the DateValue function to the date in the IF statement will achieve the desired result:

  A1: 2/15/91     B1: =IF(A1=DATEVALUE(&quot;2/15/91&quot;),TRUE,FALSE)

B1 now returns TRUE. The actual serial date can also be substituted for the DateValue function.