Dynamic Date manipulation in Excel VBA (Visual Basic for Applications)

Dynamic Date manipulation in Excel VBA (Visual Basic for Applications)

DateAdd

Function return a variant date in return which can be cast as per required format . In this article we are going to create one user defined function which allows to add below given date components and returns a valid date back as result:

Components

  1. Days
  2. Weeks
  3. Months,
  4. Years
  5. Quarters

Syntax

DateAdd(interval, number, date)

  1. Interval: a mandatory parameter refers to an interval of time as follow:
    • s : second
    • n: minute
    • h: hour
    • d: day
    • ww: week
    • w: week day
    • m: month
    • q: quarter
    • y: day of year
    • yyyy: year
  2. Number: a mandatory parameter, refers to a positive or negative integer to add number of intervals
  3. Date: a mandatory parameter, a date to which actual add or subtract operation needs to be performed

Code example

Public Function AddDate(cpDate As String, Optional cpNumber As Integer = 1, Optional oDays As Boolean = False, Optional oWeeks As Boolean = False, Optional oMonths As Boolean = False, Optional oYears As Boolean = False, Optional oQuarters As Boolean = False) As String
On Error GoTo errh
    AddDate = "Invlalid date"
    Dim oDate As Date
    
    'Validate date
    If IsDate(cpDate) = False Then
        Exit Function
    End If
    
    'Cast date
    oDate = CDate(cpDate)
    
    'Validate cpNumber
    If Len(cpNumber) = 0 Then
        Exit Function
    End If
    
    Dim oNumber As Integer
    'Cast number
    oNumber = CInt(cpNumber)
    
    'Validate number
    If oDays = True Then
        AddDate = Format(DateAdd("d", oNumber, oDate), "DD-MMM-YYYY")
    ElseIf oWeeks = True Then
        AddDate = Format(DateAdd("ww", oNumber, oDate), "DD-MMM-YYYY")
    ElseIf oMonths = True Then
        AddDate = Format(DateAdd("m", oNumber, oDate), "DD-MMM-YYYY")
    ElseIf oYears = True Then
        AddDate = Format(DateAdd("yyyy", oNumber, oDate), "DD-MMM-YYYY")
    ElseIf oQuarters = True Then
        AddDate = Format(DateAdd("q", oNumber, oDate), "DD-MMM-YYYY")
    End If
errh:
If Err.Number <> 0 Then
End If
End Function

Result

Implementation

Conclusion

In above code we have created a User Defined Function “AddDate” which takes following parameters:

  1. cpDate: Required, a valid date
  2. cpNumbers: Required, the number of intervals to be added in given date
  3. oDays: Optional, if need to add days pass True
  4. oWeeks: Optional, if need to add weeks pass True
  5. oMonths: Optional, if need to add months pass True
  6. oYears: Optional, if need to add years pass True
  7. oQuarters: Optional, if need to add quarters pass True

Next >> Create your own User Defined Function Excel example

Leave a Reply

Your email address will not be published.