Calculating Federal Holidays in VBA: Juneteenth Update

Mike Wolfe -

A couple of years ago, I published an article with a function that uses brute force to return a collection of federal holidays between two dates (though it only works for 1997 - 2030):

Federal Holidays in VBA
How do you calculate US federal holiday observances in VBA? One option is to use brute force and skip the calculation altogether.
No Longer SetMike Wolfe

I followed that up with a later article that uses federal law to work for all dates, going back to 1870 when the first four federal holidays were created:

Using TDD to Calculate Holidays in VBA
This article takes you step-by-step through the Test Driven Design process, unencumbered by the complexity of any sort of testing framework.
No Longer SetMike Wolfe

On the original article, commenter J. Woolley pointed out that I was missing the Juneteenth holidays.  Those were signed into law after I published my article.

I went back through the code for both articles listed above and updated them to include the Juneteenth holiday.  While I was at it, I also added year checks to all the calculated holidays.  The updated FederalHolidays function in the second article above should now be accurate all the way back through 1870 when federal holidays were first established.

Here is the Before and After views of the relevant section of the FederalHolidays function.

BEFORE

    Dim Yr As Integer
    For Yr = StartYear To EndYear
        Dim i As Integer
        For i = 1 To 10
            Dim Dt As Date
            Select Case i
            Case 1 'New Year's Day
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 1, 1))
                
            Case 2 'Birthday of Martin Luther King, Jr.
                '3rd Monday of January
                Dt = OrdinalWeekdayOfMonth(3, vbMonday, 1, Yr)
            
            Case 3 'Washington's Birthday
                '3rd Monday of February
                Dt = OrdinalWeekdayOfMonth(3, vbMonday, 2, Yr)
                
            Case 4 'Memorial Day
                'Last Monday of May
                Dt = OrdinalWeekdayOfMonth(5, vbMonday, 5, Yr)
            
            Case 5 'Independence Day
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 7, 4))
            
            Case 6 'Labor Day
                '1st Monday of September
                Dt = OrdinalWeekdayOfMonth(1, vbMonday, 9, Yr)
            
            Case 7 'Columbus Day
                '2nd Monday of October
                Dt = OrdinalWeekdayOfMonth(2, vbMonday, 10, Yr)
            
            Case 8 'Veterans Day
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 11, 11))
            
            Case 9 'Thanksgiving Day
                '4th Thursday of November
                Dt = OrdinalWeekdayOfMonth(4, vbThursday, 11, Yr)
            
            Case 10 'Christmas Day
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 12, 25))
            
            End Select
            If Dt >= StartDate And Dt <= EndDate Then
                FederalHolidays.Add Dt
            End If
        Next i
    Next Yr

AFTER

    Dim Yr As Integer
    For Yr = StartYear To EndYear
        Dim i As Integer
        For i = 1 To 11
            Dim Dt As Date
            Select Case i
            Case 1 'New Year's Day
                If Yr < 1871 Then GoTo NextHoliday
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 1, 1))
                
            Case 2 'Birthday of Martin Luther King, Jr.
                '3rd Monday of January
                If Yr < 1986 Then GoTo NextHoliday
                Dt = OrdinalWeekdayOfMonth(3, vbMonday, 1, Yr)
            
            Case 3 'Washington's Birthday
                '3rd Monday of February
                If Yr < 1879 Then GoTo NextHoliday
                Dt = OrdinalWeekdayOfMonth(3, vbMonday, 2, Yr)
                
            Case 4 'Memorial Day
                If Yr < 1868 Then GoTo NextHoliday
                If Yr < 1971 Then
                    'Observed on May 30th from 1868 to 1970
                    Dt = GetHolidayObservanceDate(DateSerial(Yr, 5, 30))
                Else
                    'Last Monday of May
                    Dt = OrdinalWeekdayOfMonth(5, vbMonday, 5, Yr)
                End If
            
            Case 5 'Juneteenth National Independence Day
                'Juneteenth was first celebrated as a federal holiday in 2021
                If Yr < 2021 Then GoTo NextHoliday
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 6, 19))
            
            Case 6 'Independence Day
                If Yr < 1870 Then GoTo NextHoliday
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 7, 4))
            
            Case 7 'Labor Day
                '1st Monday of September
                If Yr < 1894 Then GoTo NextHoliday
                Dt = OrdinalWeekdayOfMonth(1, vbMonday, 9, Yr)
            
            Case 8 'Columbus Day
                '2nd Monday of October
                If Yr < 1971 Then GoTo NextHoliday
                Dt = OrdinalWeekdayOfMonth(2, vbMonday, 10, Yr)
            
            Case 9 'Veterans Day
                If Yr < 1954 Then GoTo NextHoliday
                If Yr < 1971 Then
                    Dt = GetHolidayObservanceDate(DateSerial(Yr, 11, 11))
                ElseIf Yr < 1978 Then
                    'Observed the fourth Monday of October from 1971 - 1977
                    Dt = OrdinalWeekdayOfMonth(4, vbMonday, 10, Yr)
                Else
                    Dt = GetHolidayObservanceDate(DateSerial(Yr, 11, 11))
                End If
            
            Case 10 'Thanksgiving Day
                If Yr < 1870 Then GoTo NextHoliday
                '4th Thursday of November
                Dt = OrdinalWeekdayOfMonth(4, vbThursday, 11, Yr)
            
            Case 11 'Christmas Day
                If Yr < 1870 Then GoTo NextHoliday
                Dt = GetHolidayObservanceDate(DateSerial(Yr, 12, 25))
            
            End Select
            If Dt >= StartDate And Dt <= EndDate Then
                FederalHolidays.Add Dt
            End If
NextHoliday:
        Next i
    Next Yr

For the latest copy of the full code, visit Using TDD to Calculate Holidays in VBA.

My original FedHolidays function, which uses the brute force approach, only works for years between 1997 and 2030.  It's good for testing, but it's not a great general purpose solution, given the year constraints.

My FederalHolidays function, which calculates holidays based on US federal law, now works for all years going back to the establishment of the first federally recognized holidays in 1870.  As such, it's the better option for most purposes.

I've updated a couple of my related articles and functions to use this new version:

Counting Business Days in VBA
Counting business days is easy with the WeekDayCount() and FedHolidays() functions. What’s interesting is how we test the WorkingDayCount() function.
No Longer SetMike Wolfe
VBA IsBusinessDay() Function
A simple function that returns True except for weekends and US federal holidays.
No Longer SetMike Wolfe

Cover image created with Microsoft Designer