Hans – you wrote this marvelous piece of code for me. How could I alter this to also exclude Sundays?
Public Function sixdayworkweekbetween(rngInStart As Range, rngInEnd As Range, rngHolidays As Range) As Long
‘ Arguments are Start Date cell, End Date cell, and range of Holidays in valid Excel date format
Dim rngCell As Range
Dim lngStartDate As Long, lngEndDate As Long, lngNextDay As Long, lngWorkDayCount As Long
Dim lngIncr As Long, lngC As Long
Application.Volatile
lngStartDate = CLng(rngInStart.Value)
lngEndDate = CLng(rngInEnd.Value)
lngNextDay = lngStartDate
Do
lngNextDay = lngNextDay + 1
lngIncr = 1
If Weekday(lngNextDay, vbMonday) = 2 Then ‘ Monday, don’t count it
lngIncr = 0
Else
For Each rngCell In rngHolidays
If lngNextDay = CLng(rngCell.Value) Then ‘ Holiday, don’t count it
lngIncr = 0
Exit For
End If
Next rngCell
End If
If lngIncr = 0 Then Debug.Print “Holiday or Sunday: ” & CDate(lngNextDay)
‘ increment work day count by 1, or 0 if a Sunday or Holiday
lngWorkDayCount = lngWorkDayCount + lngIncr
Debug.Print CDate(lngNextDay); lngWorkDayCount
Loop Until lngNextDay = lngEndDate
sixdayworkweekbetween = lngWorkDayCount
End Function
Thanks!
