Back to Dates in Excel archive index

Back to archive home

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<"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?

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

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.

: 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<"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<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

I think that Excel is evaluating "12/31/2000"

as 12 divided by 31 divided by 2000. Try the

following formula:

IF(stdate.pbf<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. :

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

: I think that Excel is evaluating "12/31/2000" as 12 divided by 31 divided by 2000. Try the following formula: IF(stdate.pbf<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.

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.