Solved: Determine the Quarter for a Date based on a condition

[JimS]

I need to determine the Quarter for a date based on a “condition”.

Column-A has various statuses and Column-B is a Date Field.

I need a formula in Column-C that will determine the Quarter for the Date Field (Column-B) - in this format; Q1-12.

That’s the easy part (see formula below), the condition that I need to test for is if the Status in Column-A equals “Cancelled” and the Date is within the first 21 days of any quarter then the quarter should be equal to the previous quarter.

For example, if the status in cell A2 = Cancelled and the Date in cell B2 = 01/03/2012 then the quarter in cell C2 should be Q4-11.
If the status is anything other than Cancelled then C2 should be Q1-12.
Or if the status is equal to Cancelled but the Date is 01/22/2012 then the Quarter would be Q1-12 since the cancellation is not within the first 21 days of the quarter.

This needs to work going back several quarters, so a Cancelled on 7/6/2011 would result in Q2-11 and a Cancelled on 1/15/10 would result in Q4-09.

Again if the status does not equal cancelled or the cancellation date is not within the first 21 days of a given quarter then I just need the actual quarter for the Date.

I use this to determine the current quarter:
="Q"&ROUNDUP(MONTH(B2)/3,0)&"-"&RIGHT(YEAR(B2),2)

Thanks for any help/ideas...

JimS

[Bob Phillips]

Try this

="Q"&ROUNDUP(MONTH(IF(DAY(B2)<=21,B2-21,B2))/3,0)&"-"&RIGHT(YEAR(IF(DAY(B2)<=21,B2-21,B2)),2)

[JimS]

Thanks xld - that solves the first 21 days part.

Now I just have to work on the "check for cancelled status" piece.

Which ended up looking like this:

=IF(AND(A2="Cancelled",ROUNDUP(MONTH(IF(DAY(B2)<=21,B2-21,B2))/3,)),"Q"&ROUNDUP(MONTH(IF(DAY(B2)<=21,B2-21,B2))/3,0)&"-"&RIGHT(YEAR(IF(DAY(B2)<=21,B2-21,B2)),2),"Q"&ROUNDUP(MONTH(B2)/3,0)&"-"&RIGHT(YEAR(B2),2))

Thanks again - Have a Good New Year...

JimS