Business Days in Google Sheets with Real Bank Holidays

Google Sheets' WORKDAY and NETWORKDAYS skip weekends, but they only skip holidays you give them. Here are two ways to give them the right list, one with no account at all.

1. Free: import the Federal Reserve holidays

Create a sheet called Holidays and put this in cell A1. It pulls every Federal Reserve closure from 2000 to 2040 from the open fed-holidays dataset, which updates itself monthly:

=IMPORTDATA("https://raw.githubusercontent.com/ParsingMAINFRAME/fed-holidays/main/fed-holidays.csv")

Then use the dates in column A (skipping the header) as the holiday range:

=WORKDAY(B2, 2, Holidays!A2:A)
=NETWORKDAYS(B2, C2, Holidays!A2:A)

That's the right list for US ACH and wire dates. It's not the US federal holiday list: when a holiday falls on a Saturday the Fed stays open on Friday (why).

2. Any calendar: the Bankday API

For TARGET2, Bacs, Payments Canada, stock exchanges or 247 countries, get a free API key (1,000 requests a month) and import the holidays as CSV:

=IMPORTDATA("https://bankday.dev/v1/holidays?calendar=ca-payments&year=2027&format=csv&api_key=YOUR_KEY")

Or add two custom functions. Open Extensions → Apps Script, paste this, then add your key under Project Settings → Script properties as BANKDAY_KEY:

/**
 * Adds business days to a date on a payment calendar.
 * @param {Date} date Start date
 * @param {number} days Business days to add (negative to go back)
 * @param {string} calendar Calendar id, e.g. "us-fed" (the default), "eu-target2", "uk-bacs", "ca-payments"
 * @customfunction
 */
function BANKDAY_ADD(date, days, calendar) {
  var r = bankday_("/v1/business-days/add", { calendar: calendar || "us-fed", date: ymd_(date), days: days });
  return Utilities.parseDate(r.result, Session.getScriptTimeZone(), "yyyy-MM-dd");
}

/**
 * TRUE if the date is a business day on the calendar.
 * @customfunction
 */
function BANKDAY_ISBUSINESSDAY(date, calendar) {
  return bankday_("/v1/business-day", { calendar: calendar || "us-fed", date: ymd_(date) }).business_day;
}

function bankday_(path, params) {
  var qs = Object.keys(params).map(function (k) { return k + "=" + encodeURIComponent(params[k]); }).join("&");
  var url = path + "?" + qs, cache = CacheService.getScriptCache(), hit = cache.get(url);
  if (hit) return JSON.parse(hit);
  var res = UrlFetchApp.fetch("https://bankday.dev" + url, {
    headers: { Authorization: "Bearer " + PropertiesService.getScriptProperties().getProperty("BANKDAY_KEY") },
    muteHttpExceptions: true,
  });
  var body = JSON.parse(res.getContentText());
  if (res.getResponseCode() !== 200) throw new Error(body.error ? body.error.message : "Bankday error " + res.getResponseCode());
  cache.put(url, res.getContentText(), 21600); // answers don't change; save your quota
  return body;
}

function ymd_(d) {
  return typeof d === "string" ? d : Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
}

Then in any cell:

=BANKDAY_ADD(B2, 2, "uk-bacs")
=BANKDAY_ISBUSINESSDAY(B2, "us-fed,eu-target2")

Each new answer is one request, and repeats come from the cache for six hours, so a sheet of a few hundred dates stays well inside the free tier.

See also: Excel NETWORKDAYS with bank holidays · 2027 payroll calendars with CSV downloads · minimum wages in Google Sheets