Counting Weekdays in VBA

In recent articles, I shared functions for identifying US federal holidays and business days.  What if you need to know the number of business days between two dates?

In this article, we start the calculation by determining how many weekdays are between two dates using the WeekDayCount() function.  In tomorrow's article, we'll finish the calculation by subtracting the number of federal holidays between those same dates to arrive at a final business day count.

WeekDayCount()

In the WeekDayCount() function, we start by determining the total number of days between the two dates.

Then we count the number of "complete weeks."  Here I'm defining a "complete week" as seven consecutive days.  Every complete week--regardless of whether it starts on a Sunday and ends on a Saturday or starts on a Wednesday and ends on a Tuesday--will have exactly two non-business days: Saturday and Sunday.  

To get the count of complete weeks, we divide by 7 and discard the remainder.  We can use the backslash operator (\) to perform integer division, which always truncates the remainder.  For example, 20 \ 7 = 2.

The last thing we have to determine is how many additional Saturdays and Sundays occur between the start and end date.  For example, if the start date is a Monday and the end date is a Friday, there are no additional weekend days to subtract from our total.  However, if the start date is a Thursday and the end date is a Tuesday, we have to subtract both Saturday and Sunday from our total.

The final equation takes the total number of days between the start and end date, subtracts out the Saturday and Sunday from every complete week, then subtracts out up to two additional days depending on what days of the week the start and end dates fall.

'Returns the number of weekdays between two dates (inclusive)
Function WeekDayCount(StartDate As Date, EndDate As Date) As Long
    Dim TotalDays As Long
    TotalDays = EndDate - StartDate + 1
    
    Dim CompleteWeeks As Long
    CompleteWeeks = TotalDays \ 7 'Integer division; remainder is truncated
    
    Dim StartDayOfWeek As Integer, EndDayOfWeek As Integer
    StartDayOfWeek = Weekday(StartDate)
    EndDayOfWeek = Weekday(EndDate)
    
    Dim AddlSatSuns As Long
    If TotalDays = (CompleteWeeks * 7) Then
        AddlSatSuns = 0
    ElseIf StartDayOfWeek = vbSunday And EndDayOfWeek = vbSaturday Then
        AddlSatSuns = 2
    ElseIf StartDayOfWeek > EndDayOfWeek Then
        AddlSatSuns = 2
    ElseIf StartDayOfWeek = vbSunday Or EndDayOfWeek = vbSaturday Then
        AddlSatSuns = 1
    Else
        AddlSatSuns = 0
    End If
    
    WeekDayCount = TotalDays - (CompleteWeeks * 2) - AddlSatSuns
    
End Function

Documenting and testing the WeekDayCount() function

How can we be sure this is working correctly?  And how can we document how it's supposed to work?  The simplest way to both document and test a simple function is with a series of doc tests.

'--== Doc tests for the WeekDayCount() function: ==--
'
'>>> WeekDayCount(#11/12/2012#, #11/16/2012#)
'5
'>>> WeekDayCount(#11/17/2012#, #11/18/2012#)
'0
'>>> WeekDayCount(#11/1/2012#, #11/7/2012#)
'5
'>>> WeekDayCount(#11/16/2012#, #11/20/2012#)
'3
'>>> WeekDayCount(#11/14/2012#, #11/13/2013#)
'261
'>>> WeekDayCount(#11/17/2012#, #11/25/2012#)
'5
'>>> WeekDayCount(#11/18/2012#, #11/24/2012#)
'5
'>>> WeekDayCount(#11/18/2012#, #11/25/2012#)
'5
'>>> WeekDayCount(#11/17/2012#, #11/25/2012#)
'5
'>>> WeekDayCount(#11/14/2012#, #11/21/2012#)
'6

Image by wurliburli from Pixabay