One way would be to convert all dates to a number of days from an epoch. This would allow you to determine the interval between the two dates and test to see if non working days were in range.
Here is a very basic idea I knocked up in about 10 minutes.
[code]Const kSecondsInADay = 86400
Dim duration As Int32
Dim s, e As Date
Dim sInDays, eInDays As Int32
Dim holidayList(-1) As Date
Dim nwStartDate, nwEndDate As Date
Dim holidayDate As Date
Dim holidayCount, i As Int32
Dim hInDays As Int32
duration = 0
'start & end dates
s = New Date(2023,12, 22)
s.Hour = 0
s.Minute = 0
s.Second = 0
e = New Date(2023,12, 27)
e.Hour = 0
e.Minute = 0
e.Second = 0
'build some fake data
'build a list of public holidays
holidayList.Append(New Date(2023, 12, 25))
'find the first non working day (saturday or sunday) within the start and end dates
nwStartDate = New Date(s)
nwEndDate = New Date(e)
If (nwStartDate.DayOfWeek > 1) And (nwStartDate.DayOfWeek < 7) Then
nwStartDate.Day = nwStartDate.Day + (7 - nwStartDate.DayOfWeek)
End If
'add the non working days to the holiday list
While nwStartDate <= nwEndDate
'add the first non working day
holidayDate = New Date(nwStartDate)
If holidayList.IndexOf(holidayDate) = -1 Then
holidayList.Append(holidayDate)
End If
'add the second non working day
nwStartDate.Day = nwStartDate.Day + 1
holidayDate = New Date(nwStartDate)
If holidayList.IndexOf(holidayDate) = -1 Then
holidayList.Append(holidayDate)
End If
'skip to the following week
nwStartDate.Day = nwStartDate.Day + 6
Wend
'determine the duration
'calculate the start and end time into the number of days from our date epoch
sInDays = Floor(s.TotalSeconds / kSecondsInADay)
eInDays = Floor(e.TotalSeconds / kSecondsInADay)
duration = (eInDays - sInDays) + 1
'subtract the holidays
holidayCount = UBound(holidayList)
For i = 0 To holidayCount
hInDays = Floor(holidayList(i).TotalSeconds / kSecondsInADay)
If (hInDays >= sInDays) And (hInDays <= eInDays) Then
duration = duration - 1
End If
Next
break[/code]