This function was already in a database I now "administer" It will do the
trick
The holidays table conatins two fields a date field called holidate and a
text field that holds the holiday name for reference e.g. 1/17/2005 Dr.
Martin Luther King, Jr. Day
Public Function CountWorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo ErrorHandler
'Get the number of workdays between the given dates
'function uses the Holidays table and deducts them from the days to allow
for weekends
'and holidays when calculating deadline dates
Dim dbs As Database
Dim rstHolidays As Recordset
Dim lngIndex As Long
'Dim MyDate As Date
Dim lngNumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1
Set dbs = CurrentDB
Set rstHolidays = dbs.OpenRecordset("Holidays", dbOpenDynaset)
NumSgn = Chr(35)
StartDate = Format(StartDate, "Short Date")
For lngIndex = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(StartDate))
Case Is = 1
'Do Nothing, it is Sunday
Case Is = 7
'Do Nothing, it is Saturday
Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & StartDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
lngNumDays = lngNumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If
End Select
StartDate = DateAdd("d", 1, StartDate)
Next lngIndex
CountWorkingDays = lngNumDays
Exit Function
ErrorHandler:
'on error destroy objects and exit function... The function will return
a zero value
Set rstHolidays = Nothing
Set dbs = Nothing
Exit Function
End Function
