date into current and previous quarters


L

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!
 
Ad

Advertisements

P

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
 
L

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?
 
Ad

Advertisements

P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top