How to find the last 8 quarters and their years from the current quarter and year or a given date

0
votes

I am trying to find the last 7 quarters from the given quarter and year in MS Excel.

For eg. if I have Q1 2016 then I should be able to find Q4 2015 in one cell and Q3 2015 in another cell and so on showing the last seven quarters. This should be dynamic i.e when the current quarter changes, the year should move also wherever required.

I have been able to find the last quarter number using the following formulas:

=CHOOSE(CEILING(MONTH(E4)/3,1),1,2,3,4)

and 

=IF(F5-1=0,4,F5-1)

But I am not able keep the year dynamic.

If the current quarter is Q1 2015 then the quarters I need would be:

Q4 2015
Q3 2015
Q2 2015
Q1 2015
Q4 2014
Q3 2014
Q2 2014
1
excelexcel-formulavba
What value is in E4 and F5? – Ron Rosenfeld
Also, where do expect to "find" these "other cell"? In other words, what do you want, precisely? – Ron Rosenfeld
E4 has the current date. F5 has the choose formula.What I precisely want is that I should have a cell which as the current Quarter and Year using a date. This cell should dragable to the right so that I get last 8 quarters as shown in the question. – Kshitij Marwah
Then all you should need to do is change the ROWS($1:1) items in my answer to COLUMNS($A:A) – Ron Rosenfeld

1 Answers

0
votes

Your question is not clear. But, with some date in E4, the following formula will return Qn YYYY. If you then fill right, it will return the preceding quarters. If your layout is different, you will need to adjust the formula accordingly.

EDIT Formula adjusted to be able fill right instead of down

F5:  ="Q" & INT(MONTH(EDATE($E$4,-(COLUMNS($A:A)-1)*3))/3)+1 &" " & YEAR(EDATE($E$4,-(COLUMNS($A:A)-1)*3))

The formula subtracts a multiple of 3 months from the date in E4; and computes the quarter and year for that date. By using the ROWS function, that multiple is incremented each time you fill down one row.