Less than date

**philosman** · 06-23-2009, 01:56 AM

Trying to get a cell to return a zero is a date is less than 1/1/2010, but it is not working as it returns zero even if I enter a date after 1/1/2010. Here is what I have:

=IF(L9="","",IF(J9<"1/1/2010",0,L9*(1+M9)))

**DonkeyOte** · 06-23-2009, 02:05 AM

The way your formula is written you are comparing the cell to a Text value of "01/01/2010" ... Text is always > Numbers... and dates are Numbers, so either:

a) coerce the string to a number

=IF(L9="","",IF(J9<0+"1/1/2010",0,L9*(1+M9)))

b) use DATE function

=IF(L9="","",IF(J9<DATE(2010,1,1),0,L9*(1+M9)))

**Jbentley** · 06-23-2009, 02:06 AM

Originally Posted by philosman

Trying to get a cell to return a zero is a date is less than 1/1/2010, but it is not working as it returns zero even if I enter a date after 1/1/2010. Here is what I have:

=IF(L9="","",IF(J9<"1/1/2010",0,L9*(1+M9)))

Your formula treats the date as text.
Excel stores dates as numbers.

Try putting 1/1/2010 in a cell and then change the cell format to number to see what I mean.

you could rewrite your formula as :

=IF(ISBLANK(L9),0,IF(J9<40179,0,L9*(1+M9))))

Note: I changed the "" evaluation to test if L9 is empty and if true then the formula result is 0 (zero)

**DonkeyOte** · 06-23-2009, 02:12 AM

I think it's fair to say that if OP wanted to return 0 result for L9 being Null/Blank as opposed to a Null (as was implied in the original formula) then you need not use an IF at all:

=(N(L9)*(1+M9))*(J9>=DATE(2010,1,1))