I did test and modify code in your db. And that last one looked really good but testing for all possible permutations can be more art than science. This time I manually calculated the expected results for the given sample. Then I started over from scratch with the original code. This version returns the same values as my manual calcs.
Code:
Public Function NetWorkMinutes(rdteStart As Date, rdteEnd As Date) As Long
Dim lngFirstDayMins As Long
Dim lngLastDayMins As Long
Dim dteWork As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim intMinutes As Integer
Dim dblStartTime As Double
Dim dblEndTime As Double
'Supplied end date-time earlier or equal start date-time, so exit.
If rdteEnd <= rdteStart Then NetWorkMinutes = 0: GoTo Exit_Procedure
'If the start date is a Sunday or holiday then reset it to following day.
Do
Select Case DatePart("w", rdteStart, vbMonday)
Case 7 'Sunday
rdteStart = DateAdd("d", 1, Int(rdteStart)) + TimeValue("06:30:00")
Case Else
If IsHoliday(rdteStart) Then
Select Case DatePart("w", rdteStart, vbMonday)
Case 1
rdteStart = Int(rdteStart) + TimeValue("6:30:00") + 1
Case Else
rdteStart = Int(rdteStart) + TimeValue("10:00:00") + 1
End Select
Else
Exit Do 'Not Sunday or holiday.
End If
End Select
Loop
'If the end date is a Sunday then reset it to previous day.
Do
Select Case DatePart("w", rdteEnd, vbMonday)
Case 7 'Sunday
rdteEnd = DateAdd("d", -1, Int(rdteEnd)) + TimeValue("13:30:00")
Case Else
If IsHoliday(rdteEnd) Then
If DatePart("w", rdteEnd, vbMonday) = 1 Then
rdteEnd = DateAdd("d", -2, Int(rdteEnd)) + TimeValue("13:30:00")
Else
rdteEnd = DateAdd("d", -1, Int(rdteEnd)) + TimeValue("22:00:00")
End If
Else
Exit Do 'Not Sunday or holiday.
End If
End Select
Loop
If rdteEnd > rdteStart Then
Select Case DatePart("w", rdteStart, vbMonday)
Case 6
dblEndTime = TimeValue("13:30:00")
Case Else
dblEndTime = TimeValue("22:00:00")
End Select
Select Case DatePart("w", rdteEnd, vbMonday)
Case 6
dblStartTime = TimeValue("10:00:00")
Case Else
dblStartTime = TimeValue("06:30:00")
End Select
'Special case if adjusted date start equals adjusted date end.
'Else calculate first and last day minutes.
If Int(rdteStart) = Int(rdteEnd) Then
NetWorkMinutes = DateDiff("n", rdteStart, rdteEnd)
GoTo Exit_Procedure
Else
lngFirstDayMins = DateDiff("n", rdteStart - Int(rdteStart), dblEndTime)
lngLastDayMins = DateDiff("n", dblStartTime, rdteEnd - Int(rdteEnd))
End If
'Set the start and end dates for full work days (i.e. exclude first
'and last days) and iterate through period testing for holidays and weekends.
dteWork = Int(DateAdd("d", 1, rdteStart))
dteEnd = Int(DateAdd("d", -1, rdteEnd))
intMinutes = 0
Do Until Int(dteWork) = Int(rdteEnd)
If Not IsHoliday(dteWork) Then
Select Case DatePart("w", dteWork, vbMonday)
Case 6
dteStart = dteWork + TimeValue("10:00:00")
dteEnd = dteWork + TimeValue("13:30:00")
Case Else
dteStart = dteWork + TimeValue("6:30:00")
dteEnd = dteWork + TimeValue("22:00:00")
End Select
End If
intMinutes = intMinutes + DateDiff("n", dteStart, dteEnd)
dteWork = DateAdd("d", 1, dteWork)
Loop
NetWorkMinutes = intMinutes + lngFirstDayMins + lngLastDayMins
End If
Exit_Procedure:
Exit Function
End Function
I did only one test with a holiday in the middle of range by specifying a holiday date (10/14) in the IsHoliday function.