> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Counting Business Days in VBA
- URL: https://nolongerset.com/counting-business-days-in-vba/
- Published: 2021-02-06T02:12:36.000Z
- Updated: 2026-05-08T12:51:43.000Z
- Description: Counting business days is easy with the WeekDayCount() and FederalHolidays() functions. What's interesting is how we test the WorkingDayCount() function.
- Author: Mike Wolfe
- Tags: Code Library, #Date Functions, #Import 2026-05-20 02:59

In yesterday's article, I published my **[WeekDayCount](https://nolongerset.com/counting-weekdays-in-vba/)**() 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](https://nolongerset.com/calculating-holidays-in-vba/)**() 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.

```vba
?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](https://nolongerset.com/python-inspired-doc-tests-in-vba/).

***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

```vba
'---------------------------------------------------------------------------------------
' 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](https://pixabay.com/users/quangpraha-7201644/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3060093) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3060093)*