U.S. Holidays for WORKDAY() and WORKDAY_DIFF()

5595 1
Showing results for 
Search instead for 
Did you mean: 

A silly little base that does one thing: It generates a string of ISO-formatted holiday dates as an input to WORKDAY() and WORKDAY_DIFF(). As published, it supports current U.S. federal holidays, with options to deselect three federal holidays (Martin Luther King’s Birthday, Columbus Day, and Veterans Day) some companies do not take off, as well as two non-holidays (Friday before Labor Day and Friday after Thanksgiving) many companies do. For holidays that fall on a specific day of the month (e.g., New Year’s Day, Veterans Day, Christmas), if that date should fall on a weekend, the base returns either the previous Friday or following Monday as appropriate. It’s been lightly QAed — by which I mean I know it works for 2018 through 2021.

The base will return a string of ISO-formatted dates for periods from one to fifty years. (Caveat: There’s a good chance Airtable won’t accept fifty years’ worth of holidays at once, but I’ve not yet been interested enough to find out when it chokes.) Dates are calculated, not taken from a table, so by changing the configuration of the multi-select used to specify years desired, dates beyond 2067 can be calculated, for those who wish to plan their vacations well in advance.
Everything takes place in the [Holidays] table. To use

  1. Select the year(s) for which to generate a list of holiday dates. From one to fifty years may be specified, with the multi-select currently configured for years 2018 through 2067.
  2. Select whether dates for Martin Luther King’s Birthday, the Friday before Labor Day, Columbus Day, Veterans Day, and the Day after Thanksgiving (aka ‘Black Friday’) should be included.
  3. Copy and paste (Ctrl-C/Ctrl-V) the date string from {Holiday String} to the appropriate place within the call to WORKDAY() or WORKDAY_DIFF().

Nothing Earth-shaking, but you won’t have to look up and type those dates ever again. Extremely anal-retentive types: Feel free to incorporate the holiday-calculating algorithm within the workday function itself.

(As always, this is a shared read-only link. Open the base and duplicate it into your own workspace to be able to examine and modify field configurations.)


📅 US Holidays for WORKDAY() - Airtable

Explore the "📅 US Holidays for WORKDAY()" base on Airtable.

1 Reply 1

I just released a version supporting U.K. holidays for England/Wales, Scotland, Northern Ireland, and the Republic of Ireland. The base also includes code* to calculate the date of Easter (according to the Gregorian calendar).

*Supposedly valid for years 1900 through 2203; YMMV.