Are you a Maths Hero...?

[SjR]

Hi everyone.

I have a bit of a maths query (unless there is a VB function that will sort this for me - that would be great )
I'm reading the current date from the system, and I'd like to calculate the last complete quarter.
Eg. We're in August 2001 therefore the *last complete* quarter was Q2 2001. It doesn't matter what the format is, but basically I'd like to calculate the following:
If Month = 1, then Quarter = 4 and Year = Year-1
If Month = 7, then Quarter = 2 and Year = Year
If Month = 11, then Quarter = 3 and Year = Year
Hope this makes sense...
Any help would be very, very much appreciated.

[chrisjk]

Something like this? I've only tested it for this month (obviously)

Dim strQtr As String
Dim strYear As String
 
If Format(Date, "mm") <= 3 Then
    strQtr = "Q4"
    strYear = CStr(Format(DateAdd("y", -1, Date), "yyyy"))
ElseIf Format(Date, "mm") <= 6 Then
    strQtr = "Q1"
    strYear = CStr(Format(Date, "yyyy"))
ElseIf Format(Date, "mm") <= 9 Then
    strQtr = "Q2"
    strYear = CStr(Format(Date, "yyyy"))
ElseIf Format(Date, "mm") <= 12 Then
    strQtr = "Q3"
    strYear = CStr(Format(Date, "yyyy"))
End If
 
MsgBox strQtr & ", " & strYear