‘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.
It was a calculated error :rolleyes:
Caluclate is a better description of what it is I do when I calculate…
doco
I’ll bet DD was most polite when she pointed out the typo (and only guffawed after pressing the send button).
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
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.
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.
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.
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)