how can I calculate a Start and End Date based on Quarter End-CodePudding

I need to automatically calculate a Start Date (aka QRT_START) which is 5 years of Quarters back. A Quarter is 3 months. For example, there are 4 Quarters in a Year: March 31st, June 30th, September 30th and December 31st.  

Since we are currently in November 16th 2022, the Start Date would be December 31st 2017. So depending on whatever the current date is, the Start Date needs to go back 5 years worth of Quarters.

I also need to automatically calculate the most recent End Date (aka QRT_END). So since, we are in November 16th 2022, the End Date would be the previous quarter end before today which is September 30th 2022. I have the VBA code written below, please help me fix.

Private Function getQRT_END() As String 
    Dim endmonth As Variant
    Dim endyear As Variant
    Dim Day As Variant

    endmonth = Month(Date) - 1
    If endmonth = 0 Then
        endyear = Year(Date) - 1
        endmonth = 12
        day = 31
    Else
        endyear = Year(Date)
        If endmonth = 3 Then
            day = 31
        Else
            day = 30
        End if
        endmonth = “0” & endmonth
    End If
    getQRT_END = endyear & endmonth & day
End Function

Private Function getQRT_START() As String
    Dim startmonth As Variant
    Dim startyear As Variant
    Dim Day As Variant
    
    startyear = Year(Date) - 5
    startmonth = Month(Date)   2
    If startmonth <10 Then
        If startmonth = 3 Then
        day = 31
    Else
        day = 30
        
    End if
    startmonth  = “0” & startmonth
    Else
        day = 30
    End If

    getQRT_START = startyear & startmonth & day
End Function

CodePudding user response：

Function GetQuartal(years, data)
    d = DateAdd("yyyy", years, data)
    q = (Month(d)   2) \ 3
    qstart = DateSerial(Year(d), (q - 1) * 3   1, 1)
    qend = DateSerial(Year(d), q * 3   1, 1) - 1
    GetQuartal = Array(data, d, qstart, qend)
End Function

Sub test()
    Debug.Print "Date", "Date-5Y", "QY-5 Start", "QY-5 End"
    For Each d In Array(Date, #2/29/2000#, #12/1/2021#, #5/5/1992#)
        q = GetQuartal(-5, d)
        Debug.Print q(0), q(1), q(2), q(3)
    Next
End Sub

Date          Date-5Y       QY-5 Start    QY-5 End
17.11.2022    17.11.2017    01.10.2017    31.12.2017 
29.02.2000    28.02.1995    01.01.1995    31.03.1995 
01.12.2021    01.12.2016    01.10.2016    31.12.2016 
05.05.1992    05.05.1987    01.04.1987    30.06.1987

CodePudding user response：

You can use two functions found in my library at GitHub: VBA.Date.

? DateThisQuarterUltimo(DateAdd("yyyy", -5, Date))
2017-12-31 

? DatePreviousQuarterUltimo(DateAdd("yyyy", -5, Date))
2017-09-30

They are simple high-level functions:

' Returns the ultimo date of the quarter of the date passed.
'
' 2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateThisQuarterUltimo( _
    ByVal DateThisQuarter As Date) _
    As Date

    Dim Interval    As String
    Dim Number      As Double
    Dim ResultDate  As Date
    
    Number = 0
    Interval = IntervalSetting(DtInterval.dtQuarter)
    
    ResultDate = DateIntervalUltimo(Interval, Number, DateThisQuarter)
    
    DateThisQuarterUltimo = ResultDate
    
End Function

' Returns the ultimo date of the quarter preceding the quarter of the date passed.
'
' 2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DatePreviousQuarterUltimo( _
    ByVal DateThisQuarter As Date) _
    As Date

    Dim Interval    As String
    Dim Number      As Double
    Dim ResultDate  As Date
    
    Number = -1
    Interval = IntervalSetting(DtInterval.dtQuarter)
    
    ResultDate = DateIntervalUltimo(Interval, Number, DateThisQuarter)
    
    DatePreviousQuarterUltimo = ResultDate
    
End Function

CodePudding user response：

Haven't tested it completely

You can use DateSerial, DateAdd and DatePart to achieve what you want...

Option Explicit

Sub Sample()
    Dim D As Date
    Dim prevD As Date
    
    D = DateSerial(2022, 11, 16)
    
    '~~> Date 5 years years ago
    prevD = DateAdd("q", -(4 * 5), D)
    
    '~~> Last date of the quarter for a specific date
    Debug.Print DateAdd("q", DatePart("q", prevD), DateSerial(Year(prevD), 1, 1)) - 1
    'OUTPUT : 31-12-2017
    
    '~~> Last date of previous quarter for a specific date
    Debug.Print DateAdd("q", DatePart("q", D) - 1, DateSerial(Year(D), 1, 1)) - 1
    'OUTPUT : 30-09-2022
End Sub

Changed string part to a proper date.