Here is a function that does exactly that. Notice the use of a calendar
table to also eliminate holidays.
'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long
On Error GoTo AddWorkDays_Error
If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If
AddWorkDays = OriginalDate
Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop
AddWorkDays_Exit:
On Error GoTo 0
Exit Function
AddWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions",
vbExclamation, conMsgTitle
GoTo AddWorkDays_Exit
End Function
