Date & Time
Calculate business work hours between two dates and times (SLA timer)
Count only business-day work hours (e.g., 09:00–17:00 Mon–Fri) between ticket open and close timestamps using NETWORKDAYS.
Why (CloseTs - OpenTs) * 24 penalizes nights and weekends
A support ticket opened Monday at 15:00 and resolved Wednesday at 11:00 spans 44 wall-clock hours—but on a 09:00–17:00 business schedule, agents worked 2 hours Monday + 8 hours Tuesday + 2 hours Wednesday = 12 SLA business hours.
When both timestamps fall on workdays within business hours [DayStart, DayEnd], (NETWORKDAYS(INT(Open), INT(Close)) - 1) * WorkdayHours + ((CloseTime - OpenTime) * 24) handles same-day, multi-day, and weekend-spanning tickets in one clean algebraic expression.
| OpenTs (A) | CloseTs (B) | Workdays | Business Hours (09:00–17:00) |
|---|---|---|---|
| 2026-04-13 15:00 (Mon) | 2026-04-15 11:00 (Wed) | 3 | 12 (2 + 8 + 2) |
| 2026-04-17 16:00 (Fri) | 2026-04-20 10:30 (Mon) | 2 | 2.5 (1 + 1.5) |
| 2026-04-13 10:00 (Mon) | 2026-04-13 14:30 (Mon) | 1 | 4.5 |
SLA business-hours formula with LET and NETWORKDAYS
This compact formula requires both endpoints to fall on working days, inside the daily business window. If adding a holiday range to NETWORKDAYS, neither endpoint may be a listed holiday.
=LET(hrsDay, (D2 - C2) * 24, wd, NETWORKDAYS(INT(A2), INT(B2)), tA, A2 - INT(A2), tB, B2 - INT(B2), ROUND((wd - 1) * hrsDay + (tB - tA) * 24, 2))Compare raw wall-clock hours
Raw (B2 - A2) * 24 reports 44 elapsed hours including two overnight gaps.
=ROUND((B2 - A2) * 24, 2)Inputs and interpretation
Validate CloseTs >= OpenTs and 0 <= DayStart < DayEnd < 1. This example excludes overnight shifts and weekend/holiday endpoints. Do not apply it as a general SLA clock without splitting the interval into working-day overlaps. Local spreadsheet times have no time-zone or daylight-saving metadata; elapsed-hours subtraction assumes one consistent time basis.
Try the example
Use the original values or change them to test your understanding.