Tuesday, 13 October 2015

Counting the number of weekdays between two dates

If you want to calculate the number of weekdays between two dates in Excel, you simply use the Analysis Toolpak add-in function NetworkDays(). Unfortunately you don't have this luxury in Access if you're using version 97 or below, and even in 2000 you need an external reference. There are a number of solutions to this problem knocking around on the Internet - the most logically elegant (Mr Spock would be proud) can be found on the Access Web here. With due respect though, I'm more interested in speed than elegance so for a long time I used an alternative method that involved literally iterating through every day between the two dates and incrementing a counter if the day was a weekday. This was fine all while my dates were close together, but slowed right down as soon as you compared two dates years apart. Finally I resorted to puzzling out all possible weekday combinations and established some rules, which enabled me to write my own version which knocks both the alternatives mentioned above into a cocked hat for speed, and doesn't slow down for dates that are a long time apart. The function takes three parameters, a start date, a finish date and an optional Boolean so that you can specify if you want to include the last day in your count (this defaults to false if you omit it).

Please note: unlike the Excel function, this code does not allow for public holidays.

Function fctnNetworkDays(datFrom As Date, datTo As Date, Optional booIncEnd As Boolean = False) As Long
    Dim lngDiff As Long, booFWD As Boolean, booTWD As Boolean, bytF As Byte, bytT As Byte
    If booIncEnd Then datTo = datTo + 1
    lngDiff = DateDiff("d", datFrom, datTo, 0)
    bytF = WeekDay(datFrom, 0)
    bytT = WeekDay(datTo, 0)
    booFWD = (bytF < 6)
    booTWD = (bytT < 6)
    If booFWD And booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + lngDiff Mod 7
        If bytF > bytT Then fctnNetworkDays = fctnNetworkDays - 2
    ElseIf Not booFWD And booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) + bytF - 8
    ElseIf booFWD And Not booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) - bytT + 6
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) + (bytF <> bytT)
    End If
End Function

No comments:

Post a Comment