Calculating Federal Holidays in VBA: Juneteenth Update

An update to my VBA FederalHolidays(), IsBusinessDay(), and WorkingDayCount() functions with support for the new Juneteenth holiday.

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):

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:

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

Cover image created with Microsoft Designer

All original code samples by Mike Wolfe are licensed under CC BY 4.0