“Due in three business days” is not date + 3. Weekends and company holidays get in the way. addBusinessDays(date, n, holidays) moves a date forward or back by n business days, skipping weekends and an optional holiday list, using the spreadsheet's timezone. Companion to isLastBusinessDayOfMonth.
When to use
- Due dates, SLA deadlines, and “reply within N business days” columns
- Stepping backward from a deadline to find the last working day to act
- The same holiday list you already pass to
isLastBusinessDayOfMonth
How to use this snippet
/**
* Move `date` by `n` business days (negative = backward).
* Skips Sat/Sun and optional YYYY-MM-DD holidays. Uses the spreadsheet timezone.
*
* @param {Date} date
* @param {number} n
* @param {string[]=} holidays
* @return {Date}
*/
function addBusinessDays(date, n, holidays) {
n = Number(n) || 0;
var tz;
try {
tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
} catch (e) {
tz = Session.getScriptTimeZone();
}
var skip = {};
(holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
var pad = function (x) { return (x < 10 ? '0' : '') + x; };
var parts = Utilities.formatDate(date || new Date(), tz, 'yyyy-MM-dd').split('-');
var y = Number(parts[0]), m = Number(parts[1]), d = Number(parts[2]);
var step = n >= 0 ? 1 : -1;
var left = Math.abs(n);
while (left > 0) {
d += step;
var cur = new Date(Date.UTC(y, m - 1, d));
y = cur.getUTCFullYear();
m = cur.getUTCMonth() + 1;
d = cur.getUTCDate();
var dow = cur.getUTCDay();
var key = y + '-' + pad(m) + '-' + pad(d);
if (dow !== 0 && dow !== 6 && !skip[key]) left--;
}
// Return a Date at noon UTC on that calendar day so formatDate in tz stays on the same day.
return new Date(Date.UTC(y, m - 1, d, 12, 0, 0));
}
Example
function stampDueDates() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Tickets');
var holidays = ss.getSheetByName('Config')
.getRange('B2:B20').getDisplayValues()
.map(function (r) { return r[0]; })
.filter(String);
var data = sh.getDataRange().getValues();
var openedCol = data[0].indexOf('Opened');
var dueCol = data[0].indexOf('Due') + 1;
for (var r = 1; r < data.length; r++) {
if (!data[r][openedCol] || data[r][dueCol - 1]) continue;
var due = addBusinessDays(data[r][openedCol], 3, holidays);
sh.getRange(r + 1, dueCol).setValue(due);
}
}
Tips:
- Holidays are
'YYYY-MM-DD'strings in the spreadsheet's calendar, same format asisLastBusinessDayOfMonth. n = 0returns the same calendar day (still returned as a Date at noon UTC so timezone formatting stays put).- Negative
nwalks backward and still skips weekends and holidays. - Bound scripts use
getSpreadsheetTimeZone(). Standalone falls back toSession.getScriptTimeZone().
Tip: NitroGAS Co-Pilot can share one holiday list between this helper and your month-end guard. Paste your Config tab.
Happy Coding!
