Here is a function that wil calculate the number of "working" days between
two dates. A work day in this context is defined as all days except
Saturdays, Sundays, and any day entered as a holiday (non working day) in the
holiday table. One caution is that you should not put a Saturday or Sunday
in the holiday table as that will cause an incorrect results. That is
because it would be counted twice as a non working day. Once when extracting
week end days, and once when counting holidays.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
