Counting Business Days in VBA

Mike Wolfe -

In yesterday's article, I published my WeekDayCount() function, which returned the number of weekdays between a starting and ending date.  That function is handicapped by the fact that it does not take into account holidays.  Thus, it is an incomplete attempt to determine the number of business days between two dates.  Let's rectify that now.

Business days

Just to make sure we're all on the same page, let's define what I mean by business days.  For most purposes, a business day is any day except for the following:

  • Saturday
  • Sunday
  • Federal Holiday

Calculating a business day count between two dates

I broke this down into a three step process:

  1. Count the number of days between two dates
  2. Subtract out the Saturdays and Sundays
  3. Subtract out the holidays

I combined steps 1 and 2 into a standalone function named WeekDayCount().  Step 3 is mostly accomplished via the FederalHolidays() function I wrote about earlier.  

The FederalHolidays() function does not return a count, though.  Instead, it returns a collection of the federal holiday dates between the starting and ending dates we pass to it.  Returning a collection makes the function more flexible than if it only returned a count.  And, since collections provide a .Count property, we can simply and readably return a count if we so choose.

Verifying the calculation

There are eleven US federal holiday observances every year:

  • New Year's Day
  • Birthday of Martin Luther King, Jr.
  • Washington's Birthday
  • Memorial Day
  • Juneteenth National Independence Day
  • Independence Day
  • Labor Day
  • Columbus Day
  • Veterans Day
  • Thanksgiving Day
  • Christmas Day

There are 52 weeks in a year.  There are two non-business days per week, which gives us 104 Saturdays/Sundays per year.

There are 365 days in a non-leap year.  So, 365 days - 10 holidays - 104 weekend days = 251 business days.  Thus, in a leap year, we would have 252 business days.

Note that 52 times 7 equals 364.  This means that in a non-leap year, a complete year of 365 days will begin and end on the same day of the week.  If that day happens to be a Saturday or Sunday, then we will have only 250 business days.

?Weekday(#2/6/2011#) = vbSunday
True
?Weekday(#2/5/2012#) = vbSunday
True

Based on the above observations, a complete year could have 250, 251, or 252 working days in it.  Therefore, we will test all three conditions with our doc tests.

UPDATE [2023-02-15]: With the addition of Juneteenth in 2021, I added three doc tests to demonstrate the holiday which was not observed at the federal level before 2021.

The Code

'---------------------------------------------------------------------------------------
' Procedure : WorkingDayCount
' Date      : 11/29/2012 - 2/15/2023
' Author    : Mike
' Source    : https://nolongerset.com/counting-business-days-in-vba/
' Purpose   : Returns the number of working days between two dates (inclusive)
' Notes     - WorkingDays are defined as Monday-Friday, less Federal Holidays
'           - FederalHolidays as defined by OPM
'           - All federal holidays supported, including Juneteenth
' 2/15/2023 : Updated to use FederalHolidays() instead of FedHolidays() to work for all years
'
' Usage
'>>> WorkingDayCount(#2/1/2011#,#1/31/2012#)
'251
'>>> WorkingDayCount(#2/1/2012#,#1/31/2013#)
'252
'>>> WorkingDayCount(#2/6/2011#,#2/5/2012#)
'250
'>>> WorkingDayCount(#2/1/2023#,#1/31/2024#)
'250
'>>> WorkingDayCount(#2/1/2024#,#1/31/2025#)
'251
'>>> WorkingDayCount(#2/11/2023#,#2/10/2024#)
'249
'---------------------------------------------------------------------------------------
'
Function WorkingDayCount(StartDate As Date, EndDate As Date) As Long
    WorkingDayCount = WeekDayCount(StartDate, EndDate) - FederalHolidays(StartDate, EndDate).Count
End Function

Image by Quang Nguyen vinh from Pixabay