formula comparing two dates

Posted by Rita on March 30, 2001 8:56 AM

I have a formula that compares two dates and should return one of them but something's not working! The formula is: =IF(stdate.pbf&LT;"12/31/2000",TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY")). When the value for stdate.pbf is greater than 12/31/2000, this works. But when the value is less than 12/31/2000, the value for date1 is not returned. What am I doing wrong?

Posted by Aladin Akyurek on March 30, 2001 10:07 AM

Hi Rita

If stdate.pbf and date1 are the names that you gave to 2 different cells holding dates, then it's not obvious (to me) why your formula would not work.

What is returned instead of the value of date1 that you expect?

Aladin

Posted by Rita on March 30, 2001 10:44 AM

Well, after playing with this for awhile, I'm even more confused! In scenario #1; stdate.pbf = 4/28/1992 and date1 = 12/31/1999. Using my formula, 4/28/1992 is the value returned. In scenario #2; stdate.pbf = 2/09/2001 and date1 = 12/31/99. This time my formula returns 2/09/2001! They can't both be less than 12/31/2000.

Posted by Aladin Akyurek on March 30, 2001 1:09 PM

: I have a formula that compares two dates and should return one of them but something's not working! The formula is: =IF(stdate.pbf&LT;"12/31/2000",TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY")). When the value for stdate.pbf is greater than 12/31/2000, this works. But when the value is less than 12/31/2000, the value for date1 is not returned. What am I doing wrong?

I can't reproduce your results.

Try

=IF(stdate.pbf&LT;VALUE("31-12-2000"),TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY"))

Which date system are you using? There are 2 different systems: on the Mac it's 1904, on Windows it's 1900. You can check this out via Tools|Preferences and Tools|Options, respectively.

Aladin

Posted by Tim Francis-Wright on March 30, 2001 1:51 PM

I think that Excel is evaluating "12/31/2000"
as 12 divided by 31 divided by 2000. Try the
following formula:
IF(stdate.pbf&LT;DATEVALUE("12/31/2000"),TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY"))

DATEVALUE converts a date in text format to a
serial date number. When you input, say,
12/31/2000 into a cell, Excel is smart enough
to figure that you want a date (because you didn't
use an equal sign to start the entry). The same
logic does not apply uniformly throughout the
program, though.

Hope that helped. :

Posted by Aladin Akyurek on March 30, 2001 2:06 PM

Hi Tim

I see I forgot the behavior of this web-site concerning LT symbol. You too by the way.

Both of the following formulas return the right date value. I'm unable reproduce what she gets:

=IF(stdate.pbf LT "31-12-2000",TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D,YYYY"))

=IF(stdate.pbf LT VALUE("31-12-2000"),TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY"))

Actually, VALUE and DATEVALUE coerce double-quoted date strings into a serial number. So I don't give us much chance on this point.

Aladin

Posted by Rita on April 02, 2001 7:24 AM

: I think that Excel is evaluating "12/31/2000" as 12 divided by 31 divided by 2000. Try the following formula: IF(stdate.pbf&LT;DATEVALUE("12/31/2000"),TEXT(stdate.pbf,"MMMM D, YYYY"),TEXT(date1,"MMMM D, YYYY")) DATEVALUE converts a date in text format to a serial date number. When you input, say, 12/31/2000 into a cell, Excel is smart enough to figure that you want a date (because you didn't use an equal sign to start the entry). The same logic does not apply uniformly throughout the program, though. Hope that helped.

Thanks everyone. It's working now.