(Solved) – While function – iteratively adding days/months based on variables, accounting for max days of each month

(solved)-–-while-function-–-iteratively-adding-days/months-based-on-variables,-accounting-for-max-days-of-each-month

I would like some help with some VBA code as I am still relatively new. 🙂

Problem: Creating a VBA function that iteratively adds a number of days or months onto a base date accordingly to a ‘periodicity’ and ‘cutoff’ date. Please refer to the picture below.

The original function worked but did not account for days such as the ’30th’. For example, if the base date is 30/11/2019 and the cutoff date is 25/08/2020, it will iterate pass Feb 2020 which only has 28 max days. On the Feb iteration, the changedate will become ’28/02/2020′ and then iterate to ’28/03/2020′ in March 2020.

Therefore, I would like to save the max base ‘changeday’ and reimpose it into the final ‘changedate’. However, looking at the first row, the adjusted date returns the 1st. The final date should be ’30/08/2020′

Any help will be greatly appreciated! Thank you in advance!

Code #ver1:

Function getModifiedDate(newdate, cutoff, periodicity)
  While newdate < cutoff
    If periodicity = "Monthly" Then
        newdate = DateAdd("m", 1, newdate)
    ElseIf periodicity = "2-Monthly" Then
        newdate = DateAdd("m", 2, newdate)
    ElseIf periodicity = "Quarterly" Then
        newdate = DateAdd("m", 3, newdate)
    ElseIf periodicity = "6-Monthly" Then
        newdate = DateAdd("m", 6, newdate)
    ElseIf periodicity = "Weekly" Then
        newdate = newdate   7
    ElseIf periodicity = "Fortnightly" Then
        newdate = newdate   14

    End If
  Wend
  getModifiedDate = newdate
End Function

Code #ver2:

Function getModifiedDate(changedate, cutoff, periodicity)
  While changedate < cutoff
    If periodicity = "Monthly" Then
        changeday = Day(changedate)
        changedate = DateAdd("m", 1, changedate)
        lastmonthday = Application.WorksheetFunction.EoMonth(changedate, 0)
            If changeday >= Day(changedate) Then
                changedate = DateSerial(Year(changedate), Month(changedate), changeday)
            End If
    ElseIf periodicity = "2-Monthly" Then
        changeday = Day(changedate)
        changedate = DateAdd("m", 2, changedate)
            If changeday >= Day(changedate) Then
                changedate = DateSerial(Year(changedate), Month(changedate), changeday)
            End If
    ElseIf periodicity = "Quarterly" Then
        changeday = Day(changedate)
        changedate = DateAdd("m", 3, changedate)
            If changeday >= Day(changedate) Then
                    changedate = DateSerial(Year(changedate), Month(changedate), changeday)
                End If
    ElseIf periodicity = "6-Monthly" Then
        changeday = Day(changedate)
        changedate = DateAdd("m", 6, changedate)
            If changeday >= Day(changedate) Then
                changedate = DateSerial(Year(changedate), Month(changedate), changeday)
            End If
    ElseIf periodicity = "Weekly" Then
        changedate = changedate   7
    ElseIf periodicity = "Fortnightly" Then
        changedate = changedate   14

    End If
  Wend
  getModifiedDate = changedate
End Function


Excel screenshot example:

Leave a Reply

Your email address will not be published. Required fields are marked *