JimS
06-07-2011, 11:16 AM
I use the following formula to determine the current quarter:
=RIGHT(YEAR(fDate),4)&"-Q"&INT((MONTH(fDate)-1)/3)+1
NOTE: fDate is a cell that has =TODAY() in it.
Today this formula produces "2011-Q2" as it's result.
I need to determine the previous 7 quarters based on this formula.
I know that they are 2011-Q1, 2010-Q4, 2010-Q3, 2010-Q2, 2010-Q1, 2009-Q4, and 2009-Q3.
How can I determine the 7 previous quarters using a formula so that next quarter the previous 7 quarters will "move" forward (dropping 2009-Q3)?
I was thinking of substracting 92 days but that could produce incorrect results on the first or last day of the current quarter.
Any other ideas?
Thanks...
JimS
=RIGHT(YEAR(fDate),4)&"-Q"&INT((MONTH(fDate)-1)/3)+1
NOTE: fDate is a cell that has =TODAY() in it.
Today this formula produces "2011-Q2" as it's result.
I need to determine the previous 7 quarters based on this formula.
I know that they are 2011-Q1, 2010-Q4, 2010-Q3, 2010-Q2, 2010-Q1, 2009-Q4, and 2009-Q3.
How can I determine the 7 previous quarters using a formula so that next quarter the previous 7 quarters will "move" forward (dropping 2009-Q3)?
I was thinking of substracting 92 days but that could produce incorrect results on the first or last day of the current quarter.
Any other ideas?
Thanks...
JimS