Comparing Date

J

Jac

hi,

I received an Excel file with a formula inserted to compare a day with a
date; if the day is more than or equal to 15, then the formula will return
Yes; i think so.
The formula is =IF(Day(A1)>=DATE(2005,1,15),"Yes","No") & the date in A1 is
1/15/06 & the value returned is No.

Actually, the answer sounds weird to me cause it should return Yes instead
of No. Plus, I did try to change the > sign in the IF function to < sign then
the answer returned was Yes. So, what is all these mean? Is it possible to
compare in this way or the formula is built up with logical error? Could
anyone help?

Thanking in advanced.
 
D

David Biddulph

The answer from your DAY() function is 15 (as it gives an answer from 1 to
31. The value of DATE(2005,1,15) is 36905.

Look up the details of the functions, and of Excel's methods of expressing
dates, in Excel's help.
 
C

CLR

To explain the formula as given, just disect it.....
With 1/5/06 in A1, then =day(a1) will return 5.

=date(2005,1,15) will return 1/15/2005, which Excel sees as the number
38367, so the logic follows that the value in A1 (ie 5), is not greater than
or equal to 38367 so the formula correctly returns the result "NO".

The logic is flawed, it is trying to compare a DAY to a DATE.
Post back with exactly what you would like, and someone will construct the
correct formula for you.

hth
Vaya con Dios,
Chuck, CABGx3
 
J

Jac

Ok, thanks, David! : )


David Biddulph said:
The answer from your DAY() function is 15 (as it gives an answer from 1 to
31. The value of DATE(2005,1,15) is 36905.

Look up the details of the functions, and of Excel's methods of expressing
dates, in Excel's help.
Click to expand...
 
Top