Build a working-day math function

Northwind quotes every project in business days — “ten working days from sign-off” — but a calendar does not care about weekends, and neither does plain date arithmetic. Sheets ships a built-in WORKDAY, but it only understands a holiday range you pass in by hand each time, and it is easy to point half your formulas at a stale list.

These two custom functions pull the holiday list from one central Holidays tab, so every project plan shares the same definition of a working day. Update the tab once and every formula across the sheet follows. WORKDAYS_ADD answers “what date is ten working days out?” and WORKDAYS_BETWEEN answers “how many working days are between these two dates?”.

What you’ll need

• A Holidays tab in the same spreadsheet (or a dedicated one) with a single column of dates in column A, a header in row 1, and one real date per row. These are the days the functions treat as non-working in addition to weekends.
• The spreadsheet ID of that holiday sheet, dropped into the config constant below.

The script

// The spreadsheet that holds the Holidays tab. The dates must sit in
// column A, with a header in row 1.
const HOLIDAYS_SHEET_ID = '1abcHolidaysSheetId';

// How long to cache the holiday list, in seconds. One hour is plenty —
// holiday lists rarely change mid-day.
const CACHE_SECONDS = 3600;

/**
 * Adds (or subtracts) a number of working days to a start date, skipping
 * weekends and any date on the Holidays tab.
 *
 * @param {Date} startDate The date to count from.
 * @param {number} daysToAdd Working days to add. Use a negative number
 *   to count backwards.
 * @return {Date} The resulting working day.
 * @customfunction
 */
function WORKDAYS_ADD(startDate, daysToAdd) {
  // Bail out cleanly if the inputs are not usable.
  if (!(startDate instanceof Date) || !daysToAdd) return '';

  const holidays = getHolidays();
  const result = new Date(startDate);
  let remaining = Math.abs(daysToAdd);

  // Step forwards or backwards one calendar day at a time, only counting
  // down when we land on a working day.
  const step = daysToAdd > 0 ? 1 : -1;
  while (remaining > 0) {
    result.setDate(result.getDate() + step);
    if (isWorkday(result, holidays)) remaining--;
  }
  return result;
}

/**
 * Counts the working days between two dates, inclusive of both ends,
 * skipping weekends and any date on the Holidays tab.
 *
 * @param {Date} start The first date.
 * @param {Date} end The last date.
 * @return {number} The number of working days in the range.
 * @customfunction
 */
function WORKDAYS_BETWEEN(start, end) {
  // Bail out cleanly if either input is not a real date.
  if (!(start instanceof Date) || !(end instanceof Date)) return '';

  const holidays = getHolidays();
  let count = 0;
  const cursor = new Date(start);

  // Walk every calendar day in the range and tally the working ones.
  while (cursor <= end) {
    if (isWorkday(cursor, holidays)) count++;
    cursor.setDate(cursor.getDate() + 1);
  }
  return count;
}

/**
 * Decides whether a single date is a working day: not a weekend and not
 * on the holiday set.
 *
 * @param {Date} date The date to test.
 * @param {Set<string>} holidays ISO date strings to treat as holidays.
 * @return {boolean} True if the date is a working day.
 */
function isWorkday(date, holidays) {
  // getDay returns 0 for Sunday and 6 for Saturday.
  const day = date.getDay();
  if (day === 0 || day === 6) return false;

  // Compare on the YYYY-MM-DD portion so the time of day never matters.
  const iso = date.toISOString().slice(0, 10);
  return !holidays.has(iso);
}

/**
 * Reads the Holidays tab into a Set of ISO date strings. The result is
 * cached so a column of formulas does not re-read the sheet every cell.
 *
 * @return {Set<string>} The holiday dates as YYYY-MM-DD strings.
 */
function getHolidays() {
  const cache = CacheService.getScriptCache();
  const cached = cache.get('holidays');
  if (cached) return new Set(JSON.parse(cached));

  // Read column A, keep only real dates, and normalise them to ISO.
  const dates = SpreadsheetApp.openById(HOLIDAYS_SHEET_ID)
    .getSheets()[0]
    .getRange('A2:A')
    .getValues()
    .flat()
    .filter((value) => value instanceof Date)
    .map((value) => value.toISOString().slice(0, 10));

  cache.put('holidays', JSON.stringify(dates), CACHE_SECONDS);
  return new Set(dates);
}

How it works

1. WORKDAYS_ADD checks its inputs, loads the holiday set, then steps one calendar day at a time. It only decrements the counter when it lands on a working day, so weekends and holidays are stepped over for free. A negative daysToAdd flips the step to count backwards.
2. WORKDAYS_BETWEEN walks every day from start to end inclusive and tallies the ones isWorkday accepts.
3. isWorkday rejects Saturdays and Sundays (getDay returns 6 and 0), then compares the date — trimmed to its YYYY-MM-DD part — against the holiday set.
4. getHolidays reads column A of the holiday sheet, keeps only genuine date values, and converts them to ISO strings. It caches the result for an hour, which matters because a custom function dragged down 500 rows would otherwise re-read the sheet 500 times.

Use it

Type the formula into a cell, with the start date in A2:

=WORKDAYS_ADD(A2, 10)

That returns the date ten working days after A2. To count the working days between two dates, with the end date in B2:

=WORKDAYS_BETWEEN(A2, B2)

Both formulas can be dragged down a column. So if A2 is Friday 4 July 2025 and your Holidays tab is empty, =WORKDAYS_ADD(A2, 3) returns Wednesday 9 July — it skips Saturday and Sunday.

Watch out for

• daysToAdd of zero returns blank, not the start date. The guard treats 0 as “nothing to do”. If you need a same-day result, handle that in the spreadsheet with an IF.
• The holiday list is cached for an hour. After you edit the Holidays tab the functions keep using the old list until the cache expires. To see changes immediately, lower CACHE_SECONDS while testing or clear the cache.
• The functions only skip Saturday and Sunday. If a project runs on a different working week, you would need to adjust the day checks in isWorkday.
• toISOString works in UTC. For dates entered as plain calendar days (no time component) this is reliable, but a date carrying a late-evening timestamp in a far-west timezone could shift to the previous day. Keep holiday dates as plain dates with no time.
• Custom functions cannot read another user’s permissions. Everyone using the sheet must have at least view access to the spreadsheet named in HOLIDAYS_SHEET_ID, or the function returns an error for them.