I'm trying to calculate a formula in Excel using the IF function. Here is my
code.

=IF(B2=9/1/2006,5,IF(B2=6/1/2006,4,IF(B2=3/1/2006,3,IF(B2=1/1/2006,2,IF(B2=9/1/2005,1,0)))))

Basically this is what I want returned.

If B2=9/1/2006 then it equals 5
If B2=6/1/2006 then it equals 4
If B2=3/1/2006 then it equals 3
If B2=1/1/2006 then it equals 2
If B2=9/1/2005 then it equals 1

I have a feeling the my setup in the formula is rubbish. But what is making
me scratch my head the most is that it worked perfectly fine before! Any
help would be ultra appreciated. Thank you much! Have a nice day.

Try this:

IF(B2=DATEVALUE("9/1/2006"),5,IF(B2=DATEVALUE("6/1/2006"),4,IF(B2=DATEVALUE("3/1/2006"),3,IF(B2=DATEVALUE("1/1/2006"),2,IF(B2=DATEVALUE("9/1/2005"),1,0)))))

HTH

sqlfan13

"david@pcm" wrote:

I'm trying to calculate a formula in Excel using the IF function. Here is my
code.

=IF(B2=9/1/2006,5,IF(B2=6/1/2006,4,IF(B2=3/1/2006,3,IF(B2=1/1/2006,2,IF(B2=9/1/2005,1,0)))))

Basically this is what I want returned.

If B2=9/1/2006 then it equals 5
If B2=6/1/2006 then it equals 4
If B2=3/1/2006 then it equals 3
If B2=1/1/2006 then it equals 2
If B2=9/1/2005 then it equals 1

I have a feeling the my setup in the formula is rubbish. But what is making
me scratch my head the most is that it worked perfectly fine before! Any
help would be ultra appreciated. Thank you much! Have a nice day.

You might try:

=IF(B2=38961,5,IF(B2=38869,4,IF(B2=38777,3,IF(B 2=38718,2,IF(B2=38596,1,0)))))

HTH,
Paul
"david@pcm" wrote in message
...
I'm trying to calculate a formula in Excel using the IF function. Here is
my
code.

=IF(B2=9/1/2006,5,IF(B2=6/1/2006,4,IF(B2=3/1/2006,3,IF(B2=1/1/2006,2,IF(B2=9/1/2005,1,0)))))

Basically this is what I want returned.

If B2=9/1/2006 then it equals 5
If B2=6/1/2006 then it equals 4
If B2=3/1/2006 then it equals 3
If B2=1/1/2006 then it equals 2
If B2=9/1/2005 then it equals 1

I have a feeling the my setup in the formula is rubbish. But what is
making
me scratch my head the most is that it worked perfectly fine before! Any
help would be ultra appreciated. Thank you much! Have a nice day.

Hi David

If you wanted a formula solution rather than using multiple IF
statements, then try

=INT(MOD((MONTH(A1)+3),12)/3)+1
+4*(DATEDIF(DATE(2005,9,1),A1,"Y"))

--
Regards

Roger Govier


"david@pcm" wrote in message
...
I'm trying to calculate a formula in Excel using the IF function.
Here is my
code.

=IF(B2=9/1/2006,5,IF(B2=6/1/2006,4,IF(B2=3/1/2006,3,IF(B2=1/1/2006,2,IF(B2=9/1/2005,1,0)))))

Basically this is what I want returned.

If B2=9/1/2006 then it equals 5
If B2=6/1/2006 then it equals 4
If B2=3/1/2006 then it equals 3
If B2=1/1/2006 then it equals 2
If B2=9/1/2005 then it equals 1

I have a feeling the my setup in the formula is rubbish. But what is
making
me scratch my head the most is that it worked perfectly fine before!
Any
help would be ultra appreciated. Thank you much! Have a nice day.

=LOOKUP(B2,--{0,"9/1/5","1/1/6","3/1/6","6/1/6","9/1/6"},{0,1,2,3,4,5})