adding work days to a begin date to derive the end date

[Susan]

I currently have code to calculate the number of work days between two
dates excluding weekends and holidays. See below...

Function Work_Days(BegDate As Date, EndDate As Date) As Integer

'-----------------------------------------------------------------------'
'
'
' This function determines the number of days between two dates,
'
' accounting for weekends and holidays
'
'
'
'-----------------------------------------------------------------------'

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim Holidays As Integer

Dim cnnLocal As New ADODB.Connection
Dim rs As New ADODB.Recordset

On Error GoTo Err_Work_Days

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0

Set cnnLocal = CurrentProject.Connection
rs.Open "select HolidayDate from zHolidays where HolidayDate
between #" & BegDate & _
"# and #" & EndDate & "#;", cnnLocal, adOpenStatic,
adLockOptimistic

If rs.EOF And rs.BOF Then
'there are no holidays during the period
Else
'Debug.Print rs.GetString
Holidays = rs.RecordCount
End If

Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop

Work_Days = WholeWeeks * 5 + EndDays

If Work_Days + Holidays > 0 Then
Work_Days = WholeWeeks * 5 + EndDays - Holidays
End If

Exit Function

Err_Work_Days:

' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.

If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Function

Now what I need to do is calculate an end date starting from a
beginning date and exclude weekends and holidays. So, if I start on
10/7/2004 and want to add 5 days, 10/8/2004 would be day 1, 10/12/2004
would be day 2 (skipping Sat, Sun and Columbus Day on the Monday),
etc.

 

[Michel Walsh]

Hi,



First function at http://www.mvps.org/access/datetime/date0012.htm Take
note of the comment to get an example about how to use it with holidays.


Hoping it may help,
Vanderghast, Access MVP