Counting Weekdays in VBA
This otherwise mundane function is made a bit more interesting through its use of the little-known VBA backslash operator, which performs integer division.
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.
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