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

Share on Facebook

Tweet