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. It was a calculated error :rolleyes:

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

    doco

  2. 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

  3. 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.

  4. 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.

  5. 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.

  6. 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.

Leave a Reply

Your email address will not be published.