If function, need nested more than seven levels

[HGood]

I have many Excel tables with columns with Dates in them in mmm-yyyy format.

I'd like to insert a new column to the right of each of these columns and
enter a formula in them to convert these formats. In the new column I'd like
the result to be Q1, Q2, Q3, or Q4.

I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if it
is September, it results with Q3.

But since there are 12 months in the year, I'd need to nest 12 of these IF
functions to cover all my bases, but Excel only lets me nest 7 deep.

How can I come up with an alternate way of doing this so it will evaluate
all 12 months in one formula, and each cell in the column result in either
Q1 to Q4.

Thanks,

Harold

 

[Harald Staff]

Hi Harold

There's a numeric logic here (higher month; higher Q) that you can take
advantage of. Try
="Q"&INT((MONTH(C3)+2)/3)

HTH. Best wishes Harald

 

[HGood]

Fantastic, this did the job, thanks so much, never thought of using INT for
this.

Harold

 

[Fiona O'Grady]

Hi Harold,

A less elegant solution than Harald's, but

=IF(MONTH(C2)<=3,"Q1",(IF(MONTH(C2)<=6,"Q2",(IF(MONTH(C2)<=9,"Q3",(IF(MONTH(C2)<=12,"Q4","")))))))

also works if you want to keep your IF statements - if the month is greater
than 3 it will automatically move to the next if statement and will keep
going until it finds the correct quarter.

Fiona