DateDiff Working Days only not including holidays code problem

[Kevin]

Hi,

I have found this code to work out date difference (working days only) and
to include holidays from my [tblHolidays] table the code seems to work if
only one date in my holiday table falls between the [StartDate] and
[Enddate], but if there is more than one holiday date between [StartDate] and
[EndDate] it only seems to take off the first. (I hope this makes sense!).

Could any tell me where I am going wrong Please?



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

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

Hi,

I have found this code to work out date difference (working days only) and
to include holidays from my [tblHolidays] table the code seems to work if
only one date in my holiday table falls between the [StartDate] and
[Enddate], but if there is more than one holiday date between [StartDate]
and
[EndDate] it only seems to take off the first. (I hope this makes sense!).

Could any tell me where I am going wrong Please?



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

That is a *terribly* written function, but so far as I can tell it should
still work. I tested it, and didn't see the behavior you describe. It gave
the correct count, even when there were more than one holiday in the span of
dates.