Visual Basic Programming Code Examples

Visual Basic > Date Time Code Examples

Adding a number of weekdays to a date



Adding a number of weekdays to a date

The simple function adds a number of weekdays to a date.

'Purpose   :    Adds a number of weekdays to a specified date.
'Inputs    :    dtDate              The starting date
'               lNumDays            The number of days to add.
'Outputs   :    The starting date plus the number of specified weekdays

'Notes     :    For Excel change the header to:
'
'               Function WeekDayAdd(ByVal dtDate As Date, ByVal lNumDays As Long) As Date
'                   Dim lDaysAdded As Long, eWeekday As VbDayOfWeek
'                   Dim lAdd As Long
'                   Application.Volatile True
'                   On Error GoTo ErrFailed
'
'               To use the function in worksheets
'Revisions :

Function WeekDayAdd(ByVal dtDate As Date, ByVal lNumDays As Long) As Date
    Dim lDaysAdded As Long, eWeekday As VbDayOfWeek
    Dim lAdd As Long
    
    On Error GoTo ErrFailed
    If lNumDays < 0 Then
        lAdd = -1
    Else
        lAdd = 1
    End If
    
    Do While lDaysAdded <> lNumDays
        dtDate = dtDate + lAdd
        eWeekday = Weekday(dtDate)
        If eWeekday > vbSunday And eWeekday < vbSaturday Then
            lDaysAdded = lDaysAdded + lAdd
        End If
    Loop
    WeekDayAdd = dtDate
    Exit Function

ErrFailed:
    Debug.Print Err.Description
    Debug.Assert False
End Function