Date problem

  • Thread starter Thread starter Kelvin
  • Start date Start date
K

Kelvin

This small equation is kicking my butt. Could someone enlighten me.

I am simply trying to compare 2 dates if the date in cell c2 is less that
"12/31/08" then put the value , or else put " future "

This is what I came up with
=if(c2<12/31/08, c2,"future") c2 is in the form of 5/17/08

How do I format the comparison date? I have tried it a few ways, with no
luck.

Appreciate any help
 
You could use: =IF(C2<DATEVALUE("12/31/08"),C2,"future") or
=IF(C2<DATE(2008,12,31),C2,"future")

Tyro
 
=IF(C2<39813, C2,"future")
excel date system is based on numbers of days after 0 jan 1900
12/31/08 number of days can be found by formatting the date cell as a number
39813
the formula needs that number to be able to do a comparison.
you probably want 39814 being the date for 1 jan 09.
or have <=39813
for futher info on dates see http://www.cpearson.com/excel/datetime.htm
 
One way:

=IF(C2<DATE(2008,12,31),C2,"Future")

If C2 is empty then the logical test will be TRUE and the result will be 0.
This version is more robust:

=IF(COUNT(C2),IF(C2<DATE(2008,12,31),C2,"Future"),"")
 
The date() worked. Thanks for the tip.
Bill I will give your suggestion a try as well

Thanks for all the input, Very helpful.
 
Bill Kuunders said:
=IF(C2<39813, C2,"future")
excel date system is based on numbers of days after 0 jan 1900
12/31/08 number of days can be found by formatting the date cell as
a number 39813
the formula needs that number to be able to do a comparison.
you probably want 39814 being the date for 1 jan 09.
or have <=39813
Click to expand...
....

Not exactly readable, and it's specific to the 1900 date system, so
would fubar if the OP were using the 1904 date system. Using the DATE
function, which other respondents did, is far more robust AND easier
to read and understand.

FTHOI, the OP's formula failed because Excel treats 12/31/08 as (12
divided by 31) divided by 8. Alternatives that are more readable,

=IF("12/31/08"-C2>0, C2,"future")

=IF(C2<DATEVALUE("12/31/08"), C2,"future")

=IF(C2<VALUE("12/31/08"), C2,"future")

That is, under standard formula evaluation, Excel will convert date
strings into dates in arithmetic expressions, and it also provides
functions that convert date strings into dates.
 
Back
Top