Formula for Quarter Year

[david_j_redden]

In cell A2 of my spreadsheet is an automatic date which populates with the previous month's end date. So for this example: Q12016
I can get that simply enough with ="Q"&INT((MONTH($A$2)+2)/3)&YEAR($A$2)

In cells A4 through A12 I need the results to be

A4 Q12016 ="Q"&INT((MONTH($A$2)+2)/3)&YEAR($A$2)
A5 Q42015
A6 Q32015
A7 Q22015
A8 Q12015
A9 Q42014
A10 Q32014
A11 Q22014
A12 Q12014

Any ideas?

 

[Ron Coderre]

Using your posted scenario, either of these formulas, copied down, return the results you're looking for:

A5: ="Q"&CHOOSE(MID(A4,2,1),4&(RIGHT(A4,4)-1),1&RIGHT(A4,4),2&RIGHT(A4,4),3&RIGHT(A4,4))
or
A5: ="Q"&INT(((MONTH(EDATE($A$2,-ROWS($5:5)*3))+2)/3))&YEAR(EDATE($A$2,-ROWS($5:5)*3))

Is that something you can work with?