‘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)