Calculate Work Days Based on Different Work Weeks

[rrykos1]

Hello,

I have been searching all ofer and can not find/figure out how to calculate the number of work days between two dates excluding holidays based on a 4, 5, 6, or 7 day workweek. I have the following code now that works for a 5 day workweek but I can not figure how to revise it to work for the 4 day, 6 day and 7 day workweeks. Any help would be greatly appreciated.

VBA Funtion as follows:

Public Function Workdays(ByRef StartDate As Date, _
     ByRef EndDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    StartDate = DateValue(StartDate)
    EndDate = DateValue(EndDate)
    
    nWeekdays = Weekdays(StartDate, EndDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & StartDate _
        & "# AND [Holiday] <= #" & EndDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function


Public Function Weekdays(ByRef StartDate As Date, _
    ByRef EndDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 3
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If EndDate < StartDate Then
        dtmX = StartDate
        StartDate = EndDate
        EndDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=StartDate, _
        date2:=EndDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=StartDate, _
        date2:=EndDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=StartDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=EndDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

[zmbd]

HowTo: Calculate business days - pure SQL approach.
With a bit of work, you can modify this concept to your will.
Once you understand what is being done, you can also code this in VBA. The Holidays will be the biggest pain.