CODEWITHSUNDEEP
ms-accessvbadate
Sesame
Sesame

Reputation: 3410

Find number of weekdays/weekends in a given date range

I'm trying to find some VBA code to determine the number of week days and weekend days in a given date range using Access VBA.

For example:

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

Result should be:

Week days - 22
Weekend days - 9

Can anyone help out with this?

Upvotes: 5

Views: 3718

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

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

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: I found it simplest to calculate the partial-week weekend days by calculating the lBeginDay variable so that if the start date was Monday, lBeginDay == 5... if the start date was Friday, lBeginDay == 1, etc. Other variations should also work.

Upvotes: 5

Related Questions