Solved: Determine previous quarters from today

[JimS]

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

[Chabu]

how about
[VBA]Public Function quarter(aDate As Date) As String
Dim aYear As Integer
Dim aQuarter As String
Select Case Month(aDate)
Case 1, 2, 3
aQuarter = "-Q1"
Case 4, 5, 6
aQuarter = "-Q2"
Case 7, 8, 9
aQuarter = "-Q3"
Case 10, 11, 12
aQuarter = "-Q4"
End Select
aYear = year(aDate)
quarter = aYear & aQuarter
End Function

Public Function previousQuarter(quarter As String) As String
Dim aYear As Integer
Dim aQuarter As String
aYear = Left(quarter, 4)
aQuarter = Right(quarter, 3)
Select Case aQuarter
Case "-Q1"
aQuarter = "-Q4"
aYear = aYear - 1
Case "-Q2"
aQuarter = "-Q1"
Case "-Q3"
aQuarter = "-Q2"
Case "-Q4"
aQuarter = "-Q3"
End Select
previousQuarter = aYear & aQuarter
End Function
[/VBA]

sadly it will suffer from the year 10000 bug ;-)

Greetings