Workdays excluding week ends and public holidays.

Good day, I have set up a date table and a holiday table, The purpose of this is to produce a report to show many working days are available each month.

I have a function. which runs in three phases.

Phase 1, to see how many working days are available each month, this phase is working fine.

Phase 2, to subtract fixed public holidays from the results of phase 1, this phase is also running fine.

The issue is with phase 3, to subtract non fixed public holiday from a holiday table, The issue I am experiencing is that as well as subtracting from that month, it also subtracts for the months below that month in my table.

Public Function MyWorkdays(StartDate As Date, EndDate As Date) As Long

Dim D As Date, OKToAdd As Boolean, H As Long

MyWorkdays = 0

D = StartDate

While D <= EndDate

    OKToAdd = False

    ' Monday to Friday.Fixed date holidays (Christmas day)

    If Weekday(D) > 1 And Weekday(D) < 7 Then OKToAdd = True

    If Month(D) = 1 And Day(D) = 1 Then OKToAdd = False 'New Years Day

    If Month(D) = 3 And Day(D) = 21 Then OKToAdd = False  'Human Rights Day

    If OKToAdd Then MyWorkdays = MyWorkdays + 1

    D = D + 1

Wend

' Holidays in Table. Non fixed holidays (Easter)

H = Nz(DCount("\*", "HolidayTBL", "HolidayDate Between #" & StartDate & \_

"# AND #" & EndDate & "#"), 0)

MyWorkdays = MyWorkdays - H

End Function

I then try the function with just the two phases. Phase 1, to see how many working days are available each month, this phase is working fine. Phase 2, to subtract public holiday from a holiday table, this is still giving the same issue, that as well as subtracting from that month, it also subtracts for the months below that month in my table. It does not affect the months above.

Public Function WorkDays(ByRef StartDate As Date, _

ByRef EndDate As Date, \_

Optional ByRef strHolidayTBL As String = "HolidayTBL" \_

) As Integer

' Returns the number of workdays between StartDate

' and EndDate inclusive, Workdays excludes weekends and

' holidays. Optionally, press this function the name of table

' or query as the third argument. If you don't the default

' Is "Holidays".

On Error GoTo WorkDays\_Error

Dim nWeekdays As Integer

Dim nHolidays As Integer

Dim strWhere As String

'DateValue returns the date part only.

StartDate = DateValue(StartDate)

EndDate = DateValue(EndDate)

nWeekdays = Weekdays(StartDate, EndDate)

If nWeekdays = -1 Then

    WorkDays = -1

    GoTo Workdays\_Exit

End If

 strWhere = "[HolidayDate] >= #" & StartDate \_

    & "# AND [HolidayDate] <= #" & EndDate & "#"

' Count the number of holidays.

nHolidays = DCount(Expr:="[HolidayDate]", \_

    Domain:=strHolidayTBL, \_

    Criteria:=strWhere)

WorkDays = nWeekdays - nHolidays

Workdays_Exit:

Exit Function

WorkDays_Error:

WorkDays = -1

MsgBox "Error " & Err.Number & "; " & Err.Description, \_

    vbCritical, "WorkDays"

Resume Workdays\_Exit

End Function

Public Function Weekdays(ByRef StartDate As Date, _

ByRef EndDate As Date \_

) As Integer

On Error GoTo Weekdays\_Error

Const ncNumberOfWeekendDays As Integer = 2

Dim varDays As Variant

Dim varWeekendDays As Variant

Dim dtmx As Date

If EndDate < StartDate Then

    dtmx = StartDate

    StartDate = EndDate

    EndDate = dtmx

End If

varDays = DateDiff(Interval:="D", \_

    date1:=StartDate, \_

    date2:=EndDate) + 1

varWeekendDays = (DateDiff(Interval:="ww", \_

    date1:=StartDate, \_

    date2:=EndDate) \_

    \* ncNumberOfWeekendDays) \_

    + IIf(DatePart(Interval:="w", \_

    Date:=StartDate) = vbSunday, 1, 0) \_

    + IIf(DatePart(Interval:="w", \_

    Date:=EndDate) = vbSaturday, 1, 0)

    Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:

Exit Function

Weekdays_Error:

        Weekdays = -1

        MsgBox "Error " & Err.Number & ": " & Err.Description, \_

         vbCritical, "Weekdays"

    Resume Weekdays\_Exit

End Function

I entered the below holiday, and all dates from the 24/09/2025 where subtract by one day. (column X)

| | | HolidayTBL | | HolidayID | HolidayDate | HolidayName | | --- | --- | --- | | 14 | 24/09/2025 | Hertage Day |

| | | | Query1 | | StartDate | EndDate | X | ActualWorkdays | | --- | --- | --- | --- | | 01/01/2025 | 31/01/2025 | 23 | 23 | | 01/02/2025 | 28/02/2025 | 20 | 20 | | 01/03/2025 | 31/03/2025 | 21 | 21 | | 01/04/2025 | 30/04/2025 | 22 | 22 | | 01/05/2025 | 31/05/2025 | 22 | 22 | | 01/06/2025 | 30/06/2025 | 21 | 21 | | 01/07/2025 | 31/07/2025 | 23 | 23 | | 01/08/2025 | 31/08/2025 | 21 | 21 | | 01/09/2025 | 30/09/2025 | 21 | 22 | | 01/10/2025 | 31/10/2025 | 22 | 23 | | 01/11/2025 | 30/11/2025 | 19 | 20 | | 01/12/2025 | 31/12/2025 | 22 | 23 |

Your assistance and advice will be greatly appreciated.

Many thanks