date into current and previous quarters

[lawandgrace]

I have a date field that I want my formula(s) to look at and give me the
current quarter the date falls in, and also give me the previous 3 quarters
(showing a total of 4 quarters).

I want the formulas to adjust the quarters as I adjust the date.

So, if the date is 2/30/2009, I want my formulas to show the following:

2ND QTR 2008 3RD QTR 2008 4TH QTR 2008 2ND QTR 2009

And if I change the date to 6/30/2009, I want the formulas to show:

3RD QTR 2008 4TH QTR 2008 1ST QTR 2009 2ND QTR 2009

I want the quarters to show in this format: 2ND QTR 2009

I have tried this formula, but do not know how to adjust it to show the
previous 3 quarters:

=INT((MONTH($M$484)+2)/3)&"ND QTR "&YEAR($M$484)

Thanks - your help is always appreciated!

 

[Pete_UK]

Is your first example date meant to be 4/30/2009, i.e. 30th April
2009 ?

And why does June 30th fall into the 3rd quarter? When do your
quarters start and end?

Pete

 

[lawandgrace]

My apologies - I corrected the first example below (2ND QTR 2009 should have
been 1ST QTR 2009). Arrrghh......I tried so hard to be accurate!

In the first example, 1ST QTR 2009 reflects the quarter that 2/30/2009 falls
within, and then the quarters in front of that should reflect the 3 preceding
quarters.

Same with the second example: 2ND QTR 2009 reflects the quarter that
6/30/2009 falls within.

Does this help?

 

[Pete_UK]

I was wondering what date 2/30/2009 represented - 30th February
2009 ??

Anyway, suppose your date is in A1. I've assumed there are 91 days in
a quarter, so put the following formulae in the cells stated:

A3: ="QTR-"&INT((MONTH(A1-273)-1)/3)+1&" "&YEAR(A1-273)

B3: ="QTR-"&INT((MONTH(A1-182)-1)/3)+1&" "&YEAR(A1-182)

C3: ="QTR-"&INT((MONTH(A1-91)-1)/3)+1&" "&YEAR(A1-91)

D3: ="QTR-"&INT((MONTH(A1)-1)/3)+1&" "&YEAR(A1)

It will give you this output with 28th Feb 2009 in A1:

QTR-2 2008 QTR-3 2008 QTR-4 2008 QTR-1 2009

Not exactly the same as you wanted, but close enough.

Hope this helps.

Pete