Here's a function I found years ago.
Code:
Public Function fAddWorkdays(dtStartDate As Date, _
lngWorkDays As Long) _
As Date
'Adds the passed number of workdays to a passed date. This code uses
'fNetWorkdays(), so the assumptions of tblHoliday apply for this function
'as well. Also note that if a ZERO is entered as the lngWorkDays parameter
'the function will return the start date, if its a work day, or the first
'workday PRIOR to the dtStartdate.
'''''''''''''''''''''''''''''''''''''''''''
'Author: Brent Spaulding
'Version: 7
'Date: Aug 8 2008
'''''''''''''''''''''''''''''''''''''''''''
'Revision History:
'Ver Description
'?-4 Intial releases to UA in various threads and the Code Archive
'5 Made the function cabable of handling negative work days to add
'6 Corrected for a DIV by Zero error when 0 was entered as lngWorkdays
' as well as some buggy stuff with negative workdays
'7 Formated date literals to corrected for possible errors with
' NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
'..........................................
Dim dtEndDate As Date
Dim lngDays As Long
Dim lngSaturdays As Long
Dim lngOffset As Long
Dim lngSundays As Long
'First ... GUESS at the End Date you need to cover the workdays you are adding.
'I ASSUME that the number of days that are added will always toss you into a
'week end, then I add the number of work weeks to it the get the number of
'saturdays and sundays.
lngSaturdays = 1 + Abs(lngWorkDays) \ 5
lngSundays = lngSaturdays
dtEndDate = DateAdd("d", Sgn(lngWorkDays) * (Abs(lngWorkDays) + lngSaturdays + lngSundays), dtStartDate)
'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
'of days requested.
Do Until lngWorkDays = lngDays
'Count the number of work days between the ESTIMATED end date
'and the start date
lngDays = fNetWorkdays(dtStartDate, dtEndDate, False)
'Make an adjustment to the end date
If lngDays <> lngWorkDays Then
lngOffset = lngWorkDays - lngDays
dtEndDate = dtEndDate + lngOffset
End If
Loop
'Determine the offset direction to adjust for weekends and holidays
'the offset trys to bring the end date CLOSER to the start date.
If lngWorkDays < 0 Then lngOffset = 1 Else lngOffset = -1
'Make sure the end day is NOT a holiday and NOT a Saturday/Sunday
Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
" And Weekday([HolidayDate],1) Not In (1,7)") = 0 _
And Weekday(dtEndDate, vbMonday) < 6 '6th day of week if Mon is first day
dtEndDate = dtEndDate + lngOffset
Loop
'Once we are out of the loop, the end date should be set to the correct date
fAddWorkdays = dtEndDate
End Function