IF Function Problem Excel 2000

[Guest]

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.

 

[Guest]

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

 

[PCLIVE]

You might try:

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

HTH,
Paul

 

[Roger Govier]

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"))

 

[Lori]

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