VBA IsBusinessDay() Function

Mike Wolfe -

Business days

Oftentimes when dealing with dates, we may want to know whether a given day is a business day or not.  For most purposes, a business day is any day except for the following:

  • Saturday
  • Sunday
  • Federal Holiday

Identifying federal holidays

Identifying Saturdays and Sundays is easy.  The trickier part is identifying whether a given date is a federal holiday.  In my article yesterday, I shared a brute-force function that returns a collection of US federal holidays between two dates.  

The IsFederalHoliday() function

To test if a specific date is a federal holiday, we can call that function with the same start and end date and then check the Count property of the returned collection.

'Requires FederalHolidays() function available at:
'      https://nolongerset.com/calculating-holidays-in-vba/
Function IsFederalHoliday(AsOf As Date) As Boolean
    IsFederalHoliday = (FederalHolidays(AsOf, AsOf).Count = 1)
End Function

The IsBusinessDay() function

With the IsFederalHoliday() function in place, we can now add some Saturday/Sunday checks to identify whether a given date is a business day.

In my particular use case, I sometimes needed to treat Saturdays as business days. For example, bank branches are often open on Saturdays, but closed on Sundays and federal holidays.  So, I added an optional flag to the IsBusinessDay() function in case we want to treat Saturdays as business days.

Function IsBusinessDay(AsOf As Date, Optional CountSatAsBusDay As Boolean = False) As Boolean
    Select Case Weekday(AsOf)
    Case vbSunday
        IsBusinessDay = False
    Case vbSaturday
        If Not CountSatAsBusDay Then
            IsBusinessDay = False
        Else
            IsBusinessDay = True
        End If
    Case Else
        IsBusinessDay = True
    End Select
    If IsBusinessDay Then
        'Make sure it's not a Federal Holiday
        IsBusinessDay = (Not IsFederalHoliday(AsOf))
    End If
End Function

The Code

The code below combines the two functions above.  This version of the code also includes Python-inspired doc tests.  Note that you will also need a copy of the FederalHolidays() function. 

'---------------------------------------------------------------------------------------
' Procedure : IsBusinessDay
' Author    : Mike
' Date      : 11/20/2014
' Purpose   : Returns True if the AsOf date is a business day.
' Notes     - Sundays always return False.
'           - Saturdays return False by default, but optional param allows that to be changed.
'           - Federal Holidays (as published by OPM) return False.
'           - All other days return True.
'>>> IsBusinessDay(#9/11/2001#)
' True
'>>> IsBusinessDay(#12/25/2014#)
' False
'>>> IsBusinessDay(#5/29/2010#) Or IsBusinessDay(#5/30/2010#) Or IsBusinessDay(#5/31/2010#)
' False
'---------------------------------------------------------------------------------------
'
Function IsBusinessDay(AsOf As Date, Optional CountSatAsBusDay As Boolean = False) As Boolean
    Select Case Weekday(AsOf)
    Case vbSunday
        IsBusinessDay = False
    Case vbSaturday
        If Not CountSatAsBusDay Then
            IsBusinessDay = False
        Else
            IsBusinessDay = True
        End If
    Case Else
        IsBusinessDay = True
    End Select
    If IsBusinessDay Then
        'Make sure it's not a Federal Holiday
        IsBusinessDay = (Not IsFederalHoliday(AsOf))
    End If
End Function

'>>> IsFederalHoliday(#7/4/2019#)
' True
'>>> IsFederalHoliday(#7/3/2019#)
' False
'>>> IsFederalHoliday(#7/4/2020#)
' False
'>>> IsFederalHoliday(#7/3/2020#)
' True
Function IsFederalHoliday(AsOf As Date) As Boolean
    IsFederalHoliday = (FederalHolidays(AsOf, AsOf).Count = 1)
End Function

Image by Free-Photos from Pixabay

UPDATE [2023-02-15]: Updated to use the FederalHolidays() function instead of FedHolidays() function because of the wider range of acceptable start and end dates.