IF AND formula help.

**Kly** · 07-23-2012, 10:30 AM

Hello,
I am trying to write a formula to help me categorize date ranges. I have four categories, one for each quarter of the year. So, 1-90 days would be a "1", 91-180 would be a "2" and so on Template.xlsxup to 365 days as a "4". I started with an IF(AND formula but it is not producing the results I expected. "1"s are fine, but where I would expect it to return a "2" it instead produces "TRUE" and then "3" and "4" come back as "FALSE". Where did I go wrong and is this the most efficient way to do this? Thanks.

=IF(D27<=90,1,IF(AND(D27>=91,D27<=180),2,)=IF(AND(D27>=181,D27>=270),3,)=IF((D27>=271),4,))

**Pete_UK** · 07-23-2012, 10:39 AM

Try it like this:

=IF(D27<=0,"",IF(D27<=90,1,IF(D27<=180,2,IF(D27<=270,3),IF(D27<=365,4,"")))))

Check what happens in a leap year, though.

Hope this helps.

Pete

**Kly** · 07-23-2012, 10:59 AM

I added that formula to my data but it says "You have entered too many arguments for this function"

**DominicJ** · 07-23-2012, 11:06 AM

=if(a9<90,1,if(a9<180,2,if(a9<270,3,4)))

Attempts to include 0 lead to True/False responses

**Kly** · 07-23-2012, 11:11 AM

That one does the trick, Thanks to both of you

**Pete_UK** · 07-23-2012, 11:11 AM

Sorry, I left a bracket in the middle. Try this:

=IF(D27<=0,"",IF(D27<=90,1,IF(D27<=180,2,IF(D27<=270,3,IF(D27<=365,4,"")))))

Hope this helps.

Pete