# Caluclate Calculate Last Quarter End

Function GetLastQuarterEnd(Optional ByVal dtDate As Date) As Date

‘Returns a date equal to the day on which the last calendar quarter ended.
‘dtDate is optional and the current date is used if dtDate is not supplied.
‘If dtDate is a quarter end date, the previous quarter end date is returned.

Dim lLastQtrMnth As Long
Dim dtLastQtrDay As Date

‘If no argument supplied, use today
If dtDate = 0 Then
dtDate = Now
End If

‘Calc the month the last quarter ended except for quarter 1
lLastQtrMnth = (Round((Month(dtDate) / 3) + 0.49, 0) – 1) * 3

If lLastQtrMnth = 0 Then
dtLastQtrDay = DateSerial(Year(dtDate) – 1, 12, 31)
Else
dtLastQtrDay = DateSerial(Year(dtDate), lLastQtrMnth + 1, 0)
End If

GetLastQuarterEnd = dtLastQtrDay

End Function

Update: Spellchecking only works when you use it. Thanks for catch, Double D.

Posted in Uncategorized

## 7 thoughts on “Caluclate Calculate Last Quarter End”

1. doco says:

It was a calculated error :rolleyes:

Caluclate is a better description of what it is I do when I calculate…

doco

2. Jon Peltier says:

I’ll bet DD was most polite when she pointed out the typo (and only guffawed after pressing the send button).

3. Vasant says:

A bit more concise but also more opaque (but I think it works):

Function GetLastQuarterEnd(Optional ByVal dtDate As Date) As Date

‘Returns a date equal to the day on which the last calendar quarter ended.
‘dtDate is optional and the current date is used if dtDate is not supplied.
‘If dtDate is a quarter end date, the previous quarter end date is returned.

‘If no argument supplied, use today

If dtDate = 0 Then dtDate = Date

‘Calc the date the last quarter ended

GetLastQuarterEnd = DateSerial(Year(dtDate), Month(dtDate) _
– ((Month(dtDate) Mod 3) + 2) Mod 3, 1) – 1

End Function

4. Vasant says:

Actually, the above translates easily into a worksheet formula:

=DATE(YEAR(A1),MONTH(A1)-MOD(MOD(MONTH(A1),3)+2,3),1)-1

with A1 containing dtDate.

5. Dick Kusleika says:

Vasant: That’s just the kind of slick formula I was looking for, but I couldn’t come up with it. So I chickened out and took the long road.

6. Ganain says:

Is there a way to multiply that worksheet formula to calculcate the current quarters end date? For some reason I’m asbsolutely stumped on this.

7. Bryan Festa says:

I made an attempt and this is what I came up with to calculate current quarter.
Seems to work for me:

=DATE(YEAR(TODAY()),MONTH(TODAY())+MOD(MOD(MONTH(TODAY()),3)+2,3)+1,0)

or

=DATE(YEAR(A1),MONTH(A1)+MOD(MOD(MONTH(A1),3)+2,3)+1,0)

Posting code? Use <pre> tags for VBA and <code> tags for inline.