Using IF formula with dates

[hayley]

Hi there,

Can anyone help!

I am trying to create the following formula :

A1 = 01.08.02

IF (A1="01.08.02","N","S")

As A1 does = 01.08.02 it should have "N" in the cell which I am usin
the formula in shouldn't it ? or is there something I'm missing.

The dates are exactly the same format in the formula and in the A
cell.

Any help would be very much appreciated!!

 

[mseyf]

if excel is treating 01.08.02 as a date (01-Aug-2003), test for the dat
serial number, i.e. =IF(A1=37469,"N","S"), or try formatting cell A1 a
General or Text

 

[Dave Peterson]

Or

=IF(A1=DATE(2002,8,1),"N","S")

Date(yyyy,mm,dd)

Not sure if this is August 1 or January 8th.

 

[hayley]

Thanks for that, my report can be finished a bit quicker now

 

[chadt74]

Well I'm betting that your formula is correct, BUT I'll bet that the
format of the cell (a1)is incorrect...

by using the "01.08.02" in your IF statement you are telling the IF
statement to basically look for text in the cell NOT a date. My
assumption is that the cell in A1 is a DATE and not text. To
illustrate my point do the following...

Open a new workbook

In A2 type IF(A1="01.08.02","N","S")
copy and paste into B2

In A1 type 01.08.02 this should go in as general format and you
should get a "N"
In B1 type 11/08/02 this will show up as a date (11/08/02 or something
like that), with the pointer in cell B1 hold down CTRL an press 1 to
get the format cells box. Go to the number tab and at the bottom of
the first list on the left (Category) click on Custom. Right under
the word TYPE (right hand side)there should be a text box, highlight
what ever is in there and type the following :

MM.DD.YY

and click OK

This will give you 2 cells that look exactly alike but one should be
text and the other should be a date and the formula in A2 should be N
and the formula in B2 should be S.

Now if this is correct we can take it to the next level (if you
want)... how do I get my formula to work with the date format instead
of text (which it appears to already do).

Try this formula in B2 :

IF(B1=DATE(2002,1,8),"n","s")

And that SHOULD return the elusive N.

Or you could just reformate the cell A1 to be text and reenter the
string?!?

Hope this helps.