Federal Holidays in VBA

Mike Wolfe -

The United States has ten federal holidays each year (not counting Inauguration Day, which is celebrated as a holiday for federal employees within the DC area every four years).  Those holidays are:

  • 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

The federal Office of Personnel Management website lists the dates that each of these holidays is observed for pay and leave purposes through the year 2030.  

Those dates of observance are followed by many other state and local governments within the United States, as well as some businesses, such as banks.

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

Brute force approach

I used the federal holiday schedule published at the OPM website to implement a brute-force solution for determining whether a given date is a federal holiday or not.  When I first wrote the function in 2012, the OPM website had published dates from 1997 through 2020.  They just recently updated their site to only show dates from 2011 through 2030.

The function below returns a Collection of federal holiday dates between a given start date and end date.  With this as a starting point, we can do some interesting things.  In a series of future articles, I'll expand on this topic to introduce some associated functions and follow-on topics, to include:

  • IsBusinessDay() function that returns true if the passed day is a working day
  • IsLastBusinessDayOfMonth() for when you need to know if the passed day is--you guessed it--the last business day of the month
  • WorkingDayCount() function that returns the number of working days between two dates
  • FederalHolidays(): a new function that will use current US law to project future holidays beyond 2030; I will use the brute force approach below to confirm the new function works properly by setting up an automated test
  • Juneteenth Federal Holiday: in 2021, President Biden signed into law the Juneteenth National Independence Day Act, establishing June 19 as a federal holiday to celebrate the end of slavery in the United States

The Code

Since this function is only valid for years 1997 - 2030, I've added guard clauses that will throw errors if the function is called with dates outside of that range.

It's always better to fail loudly than mislead silently.
'Returns a collection of dates that represent federal holidays as published by OPM
'  source: http://www.opm.gov/policy-data-oversight/snow-dismissal-procedures/federal-holidays/
'To get a count of Federal Holidays between a Start and End Date, use the following:
'>>>> FedHolidays(#11/1/2012#,#11/30/2012#).Count
'2
' 11/29/12  : Years 1997 - 2020 included (original source: http://www.opm.gov/policy-data-oversight/snow-dismissal-procedures/federal-holidays/)
'  2/ 1/21  : Years 2021 - 2030 added (original source: https://www.opm.gov/policy-data-oversight/pay-leave/federal-holidays/)
'  2/14/23  : Updated to include Juneteenth observances, which became federal law beginning in 2021
Function FedHolidays(Optional StartDate, Optional EndDate) As Collection
    Const EarliestDate As Date = #1/1/1997#
    Const LatestDate As Date = #12/31/2030#
    
    If Not IsMissing(StartDate) Then
        If StartDate < EarliestDate Or StartDate > LatestDate Then Throw "StartDate ({0}) outside range of maintained Federal Holidays ({1} - {2})", StartDate, EarliestDate, LatestDate
    End If
    If Not IsMissing(EndDate) Then
        If EndDate < EarliestDate Or EndDate > LatestDate Then Throw "EndDate ({0}) outside range of maintained Federal Holidays ({1} - {2})", EndDate, EarliestDate, LatestDate
    End If
    
    Dim AllHol(1 To 350) As Date
    Set FedHolidays = New Collection

    AllHol(1) = #1/1/1997#: AllHol(2) = #1/20/1997#: AllHol(3) = #2/17/1997#: AllHol(4) = #5/26/1997#: AllHol(5) = #7/4/1997#
    AllHol(6) = #9/1/1997#: AllHol(7) = #10/13/1997#: AllHol(8) = #11/11/1997#: AllHol(9) = #11/27/1997#: AllHol(10) = #12/25/1997#
    AllHol(11) = #1/1/1998#: AllHol(12) = #1/19/1998#: AllHol(13) = #2/16/1998#: AllHol(14) = #5/25/1998#: AllHol(15) = #7/3/1998#
    AllHol(16) = #9/7/1998#: AllHol(17) = #10/12/1998#: AllHol(18) = #11/11/1998#: AllHol(19) = #11/26/1998#: AllHol(20) = #12/25/1998#
    AllHol(21) = #1/1/1999#: AllHol(22) = #1/18/1999#: AllHol(23) = #2/15/1999#: AllHol(24) = #5/31/1999#: AllHol(25) = #7/5/1999#
    AllHol(26) = #9/6/1999#: AllHol(27) = #10/11/1999#: AllHol(28) = #11/11/1999#: AllHol(29) = #11/25/1999#: AllHol(30) = #12/24/1999#
    AllHol(31) = #12/31/1999#: AllHol(32) = #1/17/2000#: AllHol(33) = #2/21/2000#: AllHol(34) = #5/29/2000#: AllHol(35) = #7/4/2000#
    AllHol(36) = #9/4/2000#: AllHol(37) = #10/9/2000#: AllHol(38) = #11/10/2000#: AllHol(39) = #11/23/2000#: AllHol(40) = #12/25/2000#
    AllHol(41) = #1/1/2001#: AllHol(42) = #1/15/2001#: AllHol(43) = #2/19/2001#: AllHol(44) = #5/28/2001#: AllHol(45) = #7/4/2001#
    AllHol(46) = #9/3/2001#: AllHol(47) = #10/8/2001#: AllHol(48) = #11/12/2001#: AllHol(49) = #11/22/2001#: AllHol(50) = #12/25/2001#
    AllHol(51) = #1/1/2002#: AllHol(52) = #1/21/2002#: AllHol(53) = #2/18/2002#: AllHol(54) = #5/27/2002#: AllHol(55) = #7/4/2002#
    AllHol(56) = #9/2/2002#: AllHol(57) = #10/14/2002#: AllHol(58) = #11/11/2002#: AllHol(59) = #11/28/2002#: AllHol(60) = #12/25/2002#
    AllHol(61) = #1/1/2003#: AllHol(62) = #1/20/2003#: AllHol(63) = #2/17/2003#: AllHol(64) = #5/26/2003#: AllHol(65) = #7/4/2003#
    AllHol(66) = #9/1/2003#: AllHol(67) = #10/13/2003#: AllHol(68) = #11/11/2003#: AllHol(69) = #11/27/2003#: AllHol(70) = #12/25/2003#
    AllHol(71) = #1/1/2004#: AllHol(72) = #1/19/2004#: AllHol(73) = #2/16/2004#: AllHol(74) = #5/31/2004#: AllHol(75) = #7/5/2004#
    AllHol(76) = #9/6/2004#: AllHol(77) = #10/11/2004#: AllHol(78) = #11/11/2004#: AllHol(79) = #11/25/2004#: AllHol(80) = #12/24/2004#
    AllHol(81) = #12/31/2004#: AllHol(82) = #1/17/2005#: AllHol(83) = #2/21/2005#: AllHol(84) = #5/30/2005#: AllHol(85) = #7/4/2005#
    AllHol(86) = #9/5/2005#: AllHol(87) = #10/10/2005#: AllHol(88) = #11/11/2005#: AllHol(89) = #11/24/2005#: AllHol(90) = #12/26/2005#
    AllHol(91) = #1/2/2006#: AllHol(92) = #1/16/2006#: AllHol(93) = #2/20/2006#: AllHol(94) = #5/29/2006#: AllHol(95) = #7/4/2006#
    AllHol(96) = #9/4/2006#: AllHol(97) = #10/9/2006#: AllHol(98) = #11/10/2006#: AllHol(99) = #11/23/2006#: AllHol(100) = #12/25/2006#
    AllHol(101) = #1/1/2007#: AllHol(102) = #1/15/2007#: AllHol(103) = #2/19/2007#: AllHol(104) = #5/28/2007#: AllHol(105) = #7/4/2007#
    AllHol(106) = #9/3/2007#: AllHol(107) = #10/8/2007#: AllHol(108) = #11/12/2007#: AllHol(109) = #11/22/2007#: AllHol(110) = #12/25/2007#
    AllHol(111) = #1/1/2008#: AllHol(112) = #1/21/2008#: AllHol(113) = #2/18/2008#: AllHol(114) = #5/26/2008#: AllHol(115) = #7/4/2008#
    AllHol(116) = #9/1/2008#: AllHol(117) = #10/13/2008#: AllHol(118) = #11/11/2008#: AllHol(119) = #11/27/2008#: AllHol(120) = #12/25/2008#
    AllHol(121) = #1/1/2009#: AllHol(122) = #1/19/2009#: AllHol(123) = #2/16/2009#: AllHol(124) = #5/25/2009#: AllHol(125) = #7/3/2009#
    AllHol(126) = #9/7/2009#: AllHol(127) = #10/12/2009#: AllHol(128) = #11/11/2009#: AllHol(129) = #11/26/2009#: AllHol(130) = #12/25/2009#
    AllHol(131) = #1/1/2010#: AllHol(132) = #1/18/2010#: AllHol(133) = #2/15/2010#: AllHol(134) = #5/31/2010#: AllHol(135) = #7/5/2010#
    AllHol(136) = #9/6/2010#: AllHol(137) = #10/11/2010#: AllHol(138) = #11/11/2010#: AllHol(139) = #11/25/2010#: AllHol(140) = #12/24/2010#
    AllHol(141) = #12/31/2010#: AllHol(142) = #1/17/2011#: AllHol(143) = #2/21/2011#: AllHol(144) = #5/30/2011#: AllHol(145) = #7/4/2011#
    AllHol(146) = #9/5/2011#: AllHol(147) = #10/10/2011#: AllHol(148) = #11/11/2011#: AllHol(149) = #11/24/2011#: AllHol(150) = #12/26/2011#
    AllHol(151) = #1/2/2012#: AllHol(152) = #1/16/2012#: AllHol(153) = #2/20/2012#: AllHol(154) = #5/28/2012#: AllHol(155) = #7/4/2012#
    AllHol(156) = #9/3/2012#: AllHol(157) = #10/8/2012#: AllHol(158) = #11/12/2012#: AllHol(159) = #11/22/2012#: AllHol(160) = #12/25/2012#
    AllHol(161) = #1/1/2013#: AllHol(162) = #1/21/2013#: AllHol(163) = #2/18/2013#: AllHol(164) = #5/27/2013#: AllHol(165) = #7/4/2013#
    AllHol(166) = #9/2/2013#: AllHol(167) = #10/14/2013#: AllHol(168) = #11/11/2013#: AllHol(169) = #11/28/2013#: AllHol(170) = #12/25/2013#
    AllHol(171) = #1/1/2014#: AllHol(172) = #1/20/2014#: AllHol(173) = #2/17/2014#: AllHol(174) = #5/26/2014#: AllHol(175) = #7/4/2014#
    AllHol(176) = #9/1/2014#: AllHol(177) = #10/13/2014#: AllHol(178) = #11/11/2014#: AllHol(179) = #11/27/2014#: AllHol(180) = #12/25/2014#
    AllHol(181) = #1/1/2015#: AllHol(182) = #1/19/2015#: AllHol(183) = #2/16/2015#: AllHol(184) = #5/25/2015#: AllHol(185) = #7/3/2015#
    AllHol(186) = #9/7/2015#: AllHol(187) = #10/12/2015#: AllHol(188) = #11/11/2015#: AllHol(189) = #11/26/2015#: AllHol(190) = #12/25/2015#
    AllHol(191) = #1/1/2016#: AllHol(192) = #1/18/2016#: AllHol(193) = #2/15/2016#: AllHol(194) = #5/30/2016#: AllHol(195) = #7/4/2016#
    AllHol(196) = #9/5/2016#: AllHol(197) = #10/10/2016#: AllHol(198) = #11/11/2016#: AllHol(199) = #11/24/2016#: AllHol(200) = #12/26/2016#
    AllHol(201) = #1/2/2017#: AllHol(202) = #1/16/2017#: AllHol(203) = #2/20/2017#: AllHol(204) = #5/29/2017#: AllHol(205) = #7/4/2017#
    AllHol(206) = #9/4/2017#: AllHol(207) = #10/9/2017#: AllHol(208) = #11/10/2017#: AllHol(209) = #11/23/2017#: AllHol(210) = #12/25/2017#
    AllHol(211) = #1/1/2018#: AllHol(212) = #1/15/2018#: AllHol(213) = #2/19/2018#: AllHol(214) = #5/28/2018#: AllHol(215) = #7/4/2018#
    AllHol(216) = #9/3/2018#: AllHol(217) = #10/8/2018#: AllHol(218) = #11/12/2018#: AllHol(219) = #11/22/2018#: AllHol(220) = #12/25/2018#
    AllHol(221) = #1/1/2019#: AllHol(222) = #1/21/2019#: AllHol(223) = #2/18/2019#: AllHol(224) = #5/27/2019#: AllHol(225) = #7/4/2019#
    AllHol(226) = #9/2/2019#: AllHol(227) = #10/14/2019#: AllHol(228) = #11/11/2019#: AllHol(229) = #11/28/2019#: AllHol(230) = #12/25/2019#
    AllHol(231) = #1/1/2020#: AllHol(232) = #1/20/2020#: AllHol(233) = #2/17/2020#: AllHol(234) = #5/25/2020#: AllHol(235) = #7/3/2020#
    AllHol(236) = #9/7/2020#: AllHol(237) = #10/12/2020#: AllHol(238) = #11/11/2020#: AllHol(239) = #11/26/2020#: AllHol(240) = #12/25/2020#

    AllHol(241) = #1/1/2021#: AllHol(242) = #1/18/2021#: AllHol(243) = #2/15/2021#: AllHol(244) = #5/31/2021#: AllHol(245) = #6/18/2021#: AllHol(246) = #7/5/2021#
    AllHol(247) = #9/6/2021#: AllHol(248) = #10/11/2021#: AllHol(249) = #11/11/2021#: AllHol(250) = #11/25/2021#: AllHol(251) = #12/24/2021#
    AllHol(252) = #12/31/2021#: AllHol(253) = #1/17/2022#: AllHol(254) = #2/21/2022#: AllHol(255) = #5/30/2022#: AllHol(256) = #6/20/2022#: AllHol(257) = #7/4/2022#
    AllHol(258) = #9/5/2022#: AllHol(259) = #10/10/2022#: AllHol(260) = #11/11/2022#: AllHol(261) = #11/24/2022#: AllHol(262) = #12/26/2022#
    AllHol(263) = #1/2/2023#: AllHol(264) = #1/16/2023#: AllHol(265) = #2/20/2023#: AllHol(266) = #5/29/2023#: AllHol(267) = #6/19/2023#: AllHol(268) = #7/4/2023#
    AllHol(269) = #9/4/2023#: AllHol(270) = #10/9/2023#: AllHol(271) = #11/10/2023#: AllHol(272) = #11/23/2023#: AllHol(273) = #12/25/2023#
    AllHol(274) = #1/1/2024#: AllHol(275) = #1/15/2024#: AllHol(276) = #2/19/2024#: AllHol(277) = #5/27/2024#: AllHol(278) = #6/19/2024#: AllHol(279) = #7/4/2024#
    AllHol(280) = #9/2/2024#: AllHol(281) = #10/14/2024#: AllHol(282) = #11/11/2024#: AllHol(283) = #11/28/2024#: AllHol(284) = #12/25/2024#
    AllHol(285) = #1/1/2025#: AllHol(286) = #1/20/2025#: AllHol(287) = #2/17/2025#: AllHol(288) = #5/26/2025#: AllHol(289) = #6/19/2025#: AllHol(290) = #7/4/2025#
    AllHol(291) = #9/1/2025#: AllHol(292) = #10/13/2025#: AllHol(293) = #11/11/2025#: AllHol(294) = #11/27/2025#: AllHol(295) = #12/25/2025#
    AllHol(296) = #1/1/2026#: AllHol(297) = #1/19/2026#: AllHol(298) = #2/16/2026#: AllHol(299) = #5/25/2026#: AllHol(300) = #6/19/2026#: AllHol(301) = #7/3/2026#
    AllHol(302) = #9/7/2026#: AllHol(303) = #10/12/2026#: AllHol(304) = #11/11/2026#: AllHol(305) = #11/26/2026#: AllHol(306) = #12/25/2026#
    AllHol(307) = #1/1/2027#: AllHol(308) = #1/18/2027#: AllHol(309) = #2/15/2027#: AllHol(310) = #5/31/2027#: AllHol(311) = #6/18/2027#: AllHol(312) = #7/5/2027#
    AllHol(313) = #9/6/2027#: AllHol(314) = #10/11/2027#: AllHol(315) = #11/11/2027#: AllHol(316) = #11/25/2027#: AllHol(317) = #12/24/2027#
    AllHol(318) = #12/31/2027#: AllHol(319) = #1/17/2028#: AllHol(320) = #2/21/2028#: AllHol(321) = #5/29/2028#: AllHol(322) = #6/19/2028#: AllHol(323) = #7/4/2028#
    AllHol(324) = #9/4/2028#: AllHol(325) = #10/9/2028#: AllHol(326) = #11/10/2028#: AllHol(327) = #11/23/2028#: AllHol(328) = #12/25/2028#
    AllHol(329) = #1/1/2029#: AllHol(330) = #1/15/2029#: AllHol(331) = #2/19/2029#: AllHol(332) = #5/28/2029#: AllHol(333) = #6/19/2029#: AllHol(334) = #7/4/2029#
    AllHol(335) = #9/3/2029#: AllHol(336) = #10/8/2029#: AllHol(337) = #11/12/2029#: AllHol(338) = #11/22/2029#: AllHol(339) = #12/25/2029#
    AllHol(340) = #1/1/2030#: AllHol(341) = #1/21/2030#: AllHol(342) = #2/18/2030#: AllHol(343) = #5/27/2030#: AllHol(344) = #6/19/2030#: AllHol(345) = #7/4/2030#
    AllHol(346) = #9/2/2030#: AllHol(347) = #10/14/2030#: AllHol(348) = #11/11/2030#: AllHol(349) = #11/28/2030#: AllHol(350) = #12/25/2030#


    Dim i As Integer
    If Not IsDate(StartDate) And Not IsDate(EndDate) Then
        For i = LBound(AllHol) To UBound(AllHol)
            FedHolidays.Add AllHol(i)
        Next i
    Else
        Dim SDate As Date
        If IsDate(StartDate) Then
            SDate = StartDate
        Else
            SDate = AllHol(LBound(AllHol))
        End If

        Dim EDate As Date
        If IsDate(EndDate) Then
            EDate = EndDate
        Else
            EDate = AllHol(UBound(AllHol))
        End If

        For i = LBound(AllHol) To UBound(AllHol)
            If SDate > AllHol(i) Then GoTo NextHol
            If AllHol(i) > EndDate Then Exit For
            FedHolidays.Add AllHol(i)
NextHol:
        Next i

    End If

End Function

Image by FelixMittermeier from Pixabay

UPDATE [2023-02-14]: Updated to include Juneteenth (h/t J. Woolley)