Calendar hours lie about SLAs. A ticket opened Friday at 4pm and closed Monday at 10am looks like 66 hours and feels like two. businessHoursBetween(start, end, opts) returns the working hours between two dates — configurable workday start/end, workdays, holidays, and timezone (defaults to the spreadsheet's). Builds on the same ideas as isWithinBusinessHours and addBusinessDays.
When to use
- Response-time and resolution-time columns measured in working hours
- SLA breach checks that shouldn't count nights and weekends
- The same holiday list you already use for due dates
How to use this snippet
/**
* Working hours between two dates. Negative if end < start.
*
* @param {Date|string|number} start
* @param {Date|string|number} end
* @param {{workStart?: string, workEnd?: string, workdays?: number[], holidays?: string[], timezone?: string, spreadsheet?: Spreadsheet}} [opts]
* workStart/workEnd as 'HH:mm' (default 09:00–17:00);
* workdays as Utilities 'u' weekdays (default Mon–Fri [1..5]);
* holidays as 'YYYY-MM-DD'
* @return {number} hours (fractional)
*/
function businessHoursBetween(start, end, opts) {
opts = opts || {};
if (!start || !end) return 0;
var a = start instanceof Date ? new Date(start.getTime()) : new Date(start);
var b = end instanceof Date ? new Date(end.getTime()) : new Date(end);
if (isNaN(a.getTime()) || isNaN(b.getTime())) return 0;
var sign = 1;
if (b.getTime() < a.getTime()) { var t = a; a = b; b = t; sign = -1; }
var ss = opts.spreadsheet || (typeof SpreadsheetApp !== 'undefined' ? SpreadsheetApp.getActive() : null);
var tz = opts.timezone
|| (ss && ss.getSpreadsheetTimeZone && ss.getSpreadsheetTimeZone())
|| Session.getScriptTimeZone()
|| 'America/New_York';
var workStart = opts.workStart || '09:00';
var workEnd = opts.workEnd || '17:00';
var workdays = opts.workdays || [1, 2, 3, 4, 5];
var skip = {};
(opts.holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
function toMins(hhmm) {
var p = String(hhmm).split(':');
return Number(p[0]) * 60 + Number(p[1] || 0);
}
var openM = toMins(workStart);
var closeM = toMins(workEnd);
if (closeM <= openM) throw new Error('businessHoursBetween: workEnd must be after workStart');
function dayKey(d) { return Utilities.formatDate(d, tz, 'yyyy-MM-dd'); }
function weekday(d) { return Number(Utilities.formatDate(d, tz, 'u')); }
function minuteOfDay(d) {
return Number(Utilities.formatDate(d, tz, 'H')) * 60 + Number(Utilities.formatDate(d, tz, 'm'));
}
var totalMins = 0;
var keyA = dayKey(a);
var keyB = dayKey(b);
// Iterate calendar days from keyA to keyB using UTC date parts of the keys.
var parts = keyA.split('-');
var y = Number(parts[0]), m = Number(parts[1]), d = Number(parts[2]);
for (var i = 0; i < 2000; i++) {
var key = y + '-' + (m < 10 ? '0' : '') + m + '-' + (d < 10 ? '0' : '') + d;
// Representative instant at noon UTC on this calendar day (stable for formatDate weekday in most US tz)
var noon = new Date(Date.UTC(y, m - 1, d, 12, 0, 0));
var wd = weekday(noon);
var isWork = workdays.indexOf(wd) !== -1 && !skip[key];
if (isWork) {
var from = openM;
var to = closeM;
if (key === keyA) from = Math.max(from, minuteOfDay(a));
if (key === keyB) to = Math.min(to, minuteOfDay(b));
if (to > from) totalMins += to - from;
}
if (key === keyB) break;
// next calendar day
var next = new Date(Date.UTC(y, m - 1, d + 1));
y = next.getUTCFullYear();
m = next.getUTCMonth() + 1;
d = next.getUTCDate();
}
return sign * totalMins / 60;
}
Example
function stampResponseHours() {
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 firstReplyCol = data[0].indexOf('First Reply');
var hoursCol = data[0].indexOf('Response Hours') + 1;
for (var r = 1; r < data.length; r++) {
if (!data[r][openedCol] || !data[r][firstReplyCol]) continue;
var hrs = businessHoursBetween(data[r][openedCol], data[r][firstReplyCol], {
holidays: holidays,
workStart: '09:00',
workEnd: '17:00'
});
sh.getRange(r + 1, hoursCol).setValue(Math.round(hrs * 100) / 100);
}
}
Tips:
- Default window is Mon–Fri 09:00–17:00 in the spreadsheet timezone. Override
workdaysfor support that includes Saturday. - Holidays use the same
'YYYY-MM-DD'strings asaddBusinessDays. - Partial days count only the overlap with the work window. Friday 16:00 → Monday 10:00 is 2 hours, not 66.
- Returns a signed number: swap start/end and you get the negative.
Tip: NitroGAS Co-Pilot can share one Config holiday list across this helper, addBusinessDays, and your month-end guard.
Happy Coding!
