Search for the number of weekdays / holidays in a given date range

Question

I am trying to find a VBA code to determine the number of days of the week and days off in a given date range using Access VBA.

For instance:

Begin Date - 1/1/2012
End Date - 1/31/2012

The result should be:

Week days - 22
Weekend days - 9

Can anyone help with this?

Answer 1

These two functions will calculate the number of weekdays and days off:

Function NumWeekendDays(dBegin As Date, dEnd As Date) As Long
    Dim iPartial As Integer
    Dim lBeginDay As Long
    Dim lNumWeekendDays As Long

    iPartial = DateDiff("d", dBegin, dEnd + 1) Mod 7
    lBeginDay = 6 - DatePart("w", dBegin, vbMonday)

    lNumWeekendDays = (DateDiff("d", dBegin, dEnd + 1) \ 7) * 2
    If iPartial > 0 And lBeginDay - iPartial < 0 Then
        If lBeginDay = -1 Then
            lNumWeekendDays = lNumWeekendDays + 1
        ElseIf iPartial - lBeginDay = 1 Then
            lNumWeekendDays = lNumWeekendDays + 1
        Else
            lNumWeekendDays = lNumWeekendDays + 2
        End If
    End If

    NumWeekendDays = lNumWeekendDays

End Function

Function NumWeekDays(dBegin As Date, dEnd As Date) As Long
    NumWeekDays = DateDiff("d", dBegin, dEnd + 1) - NumWeekendDays(dBegin, dEnd)
End Function

Note. It was easier for me to calculate the days of the week of the week of the week by calculating the variable lBeginDayso that if the start date was on Monday lBeginDay == 5... if the start date was on Friday, lBeginDay == 1etc. Other options should also work.