if statements & dates?

[Guest]

Is there a problem with using dates in IF statements? I'm trying to ask
Excel to record 0 if the date is 9/9/99, and if it's not 9/9/99 put in the
content of the cell:

=if(a1=9/9/99,0,a1)

It works for dates unless it's 9/9/99
Thanks
Lynn

 

[Ron Rosenfeld]

Is there a problem with using dates in IF statements? I'm trying to ask
Excel to record 0 if the date is 9/9/99, and if it's not 9/9/99 put in the
content of the cell:

=if(a1=9/9/99,0,a1)

It works for dates unless it's 9/9/99

Not really, with the syntax you've shown. When you omit the quotes in the IF
statement, Excel will interpret the value as requiring multiple divisions.

So use:

=if(a1="9/9/99",0,a1)

Better (as it will be less ambiguous):

=if(a1=date(99,9,9),0,a1)

Probably best, as it allows easy modification:

=if(a1=b1,0,a1)

With the date to be compared with in B1.


--ron