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


#1

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