← Functions

US Holidays Calculations

How to generate a list of US Federal and optional holidays in Excel using a VBA module that handles fixed, floating, and observed holiday rules.

Calculating US holidays is no easy task. Many are "floating" holidays — they don't fall on a fixed date each year. For example, Martin Luther King Day falls on the 3rd Monday of January and Presidents' Day falls on the 3rd Monday of February. The most difficult is Easter, which is based on the March Equinox and the moon.

Holiday Rules Summary

  • New Year's Day — January 1
  • Martin Luther King Day — 3rd Monday of January
  • Presidents' Day — 3rd Monday of February
  • Good Friday — 2 days before Easter Sunday
  • Memorial Day — Last Monday of May
  • Independence Day — July 4
  • Labor Day — 1st Monday of September
  • Columbus Day — 2nd Monday of October
  • Veterans Day — November 11
  • Thanksgiving Day — 4th Thursday of November
  • Black Friday — Day after Thanksgiving
  • Christmas Eve — December 24
  • Christmas — December 25

Observed Holidays

New Year, Independence Day, Veterans Day, and Christmas have observed dates when they fall on a weekend:

  • Falls on Sunday → observed the following Monday
  • Falls on Saturday → observed the preceding Friday

As a result, New Year can be observed on December 31 of the prior year if January 1 falls on Saturday.

Note on Christmas Eve: There is no clear rule for an observed Christmas Eve. If it falls on a weekend, the program does not calculate an observed date — it typically becomes a floating personal day off.

The 10 US Federal Holidays

Federal holidays apply to government employees, banks, and bond markets:

  • New Year, Martin Luther King Day, Presidents' Day, Memorial Day, Independence Day, Labor Day, Columbus Day, Veterans Day, Thanksgiving, Christmas

Many companies substitute some federal holidays (e.g., dropping MLK Day, Presidents' Day, Columbus Day, Veterans Day) for Good Friday, Black Friday, and Christmas Eve. The program lets users select which holidays to generate.

VBA Module

The module has three subroutines:

CalcObservedHoliday — checks if a holiday falls on a weekend and adjusts to the observed date.

CalcHolidays(InputYear) — calculates all holidays for a given year. Easter is computed using the Anonymous Gregorian algorithm. Floating holidays (MLK Day, Presidents' Day, Memorial Day, etc.) use Weekday and DateSerial formulas. The subroutine writes selected holidays to the output list based on checkbox choices.

HolidayList — the main module. Reads the user-specified year range and calls CalcHolidays in a loop for each year:

Sub HolidayList()
    Dim BeginYear As Integer, EndYear As Integer
    Dim i As Integer

    Range("E2:F2000").Select
    Selection.ClearContents

    BeginYear = Range("BeginYear").Value
    EndYear = Range("EndYear").Value

    Range("E2").Activate

    For i = BeginYear To EndYear
        Call CalcHolidays(i)
    Next i
End Sub

Usage Notes

  • Enable macros before running the module.
  • Enter the From Year and To Year in the input fields.
  • Check the boxes for the holidays your organization observes.
  • Click the button to generate the holiday list.
  • The Easter calculation is only valid for years after 1899. Entering an earlier year triggers an error message.
  • If the US Government changes holiday rules, the VBA module must be updated accordingly.

The generated holiday list can be passed directly to the NETWORKDAYS or WORKDAY functions as the optional holidays parameter, or used in VLOOKUP lookups for bond payment date calculations.