Kestrel Lane's help desk promises a fix within 8 business hours. The office is open 09:00 to 17:30, Monday to Friday. The SLA report showed seven of eight tickets breached, and the desk manager was asked to explain all seven.
| Measure | Breached | Breach rate |
|---|---|---|
Clock hours, =(C2-B2)*24 | 7 of 8 | 87.5% |
| Business hours, 09:00–17:30 | 2 of 8 | 25.0% |
Scope.
NETWORKDAYS,WORKDAY,MEDIANandMODwork in every version of Excel. The due-time formula in section 7 usesLET, which needs Excel 365 or 2021.For weekends other than Saturday and Sunday, use
NETWORKDAYS.INTLandWORKDAY.INTLin the same places.
01The Clock Ran Through Nights and Weekends
The report took the resolved time, subtracted the logged time and multiplied by 24. In D2, filled to D9:
=(C2-B2)*24
Kestrel Lane Help Desk, First Half of November
Eight tickets in A2:H9. B and C are real date-times, shown here with the weekday. D is the clock time between them, E the business hours inside 09:00–17:30 on weekdays, and F the time 8 business hours after logging. G is the status the report printed from column D, H the status from F. The opening time is in J1 and the closing time in J2.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
That is the right answer to a different question. It counts every hour between the two timestamps, including the nights the office was dark and the weekend nobody was in.
HD-2207 was logged at 16:45 on a Friday and fixed at 10:15 on Monday. The subtraction says 65.5 hours. The desk was open for 45 minutes on Friday and 75 minutes on Monday: 2 hours.
| Ticket | Clock hours | Business hours |
|---|---|---|
| HD-2207 | 65.50 | 2.00 |
| HD-2224 | 52.67 | 7.00 |
| HD-2212 | 20.00 | 4.50 |
Scenario: Enter 06/11/2026 16:45 in B2 and 09/11/2026 10:15 in C2. Put =(C2-B2)*24 in D2 and check that it returns 65.5, then count the office hours by hand.
02MEDIAN Clamps a Time to the Working Day
The first step is to stop hours outside 09:00–17:30 from counting. MEDIAN of three values returns the middle one, which makes it a clamp.
Put the opening time in J1 as =TIME(9,0,0) and the closing time in J2 as =TIME(17,30,0). MOD(C2,1) strips the date and keeps the time of day. Then:
=MEDIAN(MOD(C2,1),$J$1,$J$2)
A time inside the day comes back unchanged. A time before 09:00 comes back as 09:00, and a time after 17:30 as 17:30.
| Time of day | Clamped |
|---|---|
| 08:15 | 09:00 |
| 12:00 | 12:00 |
| 19:40 | 17:30 |
So HD-2236, logged at 08:15, starts counting at 09:00. HD-2219, logged at 19:40, counts nothing on its first day.
Scenario: Enter =TIME(9,0,0) in J1 and =TIME(17,30,0) in J2. Put =MEDIAN(MOD(B2,1),$J$1,$J$2) beside a ticket logged at 08:15 and check that it shows 09:00 when formatted hh:mm.
03NETWORKDAYS Counts the Days the Clock Crosses
The second step is the days in between. NETWORKDAYS(B2,C2) counts the working days from the logged day to the resolved day, both included, and skips Saturday and Sunday.
Subtract 1 and you have the number of working-day steps between the two dates. Each step is one full office day: $J$2-$J$1, or 8.5 hours.
Then correct for the clock times. Add the clamped resolved time and subtract the clamped logged time. For HD-2207:
| Piece | Value |
|---|---|
NETWORKDAYS Friday to Monday, minus 1 | 1 day, 8.5 h |
| Clamped resolved time | 10:15 |
| Clamped logged time | 16:45 |
| 8.5 + 10.25 − 16.75 | 2.0 h |
The negative part is not a mistake. It removes the hours of the Friday that came before the ticket, and of the Monday after it was fixed.
Scenario: Put =NETWORKDAYS(B2,C2) beside HD-2207 and check that it returns 2, not 4. Then change C2 to the Saturday and check that it still returns 1.
04The Whole Formula, Piece by Piece
Put together, with the result in hours, in E2 and filled to E9:
=((NETWORKDAYS(B2,C2)-1)*($J$2-$J$1)
+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$J$1,$J$2),$J$2)
-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$J$1,$J$2))*24
The first line is the full days. The second is the clamped resolved time, and the third the clamped logged time. The extra NETWORKDAYS calls deal with weekends, which section 5 explains.
NETWORKDAYS ignores the time part of a date-time, so it is safe to pass B2 and C2 whole. The *24 turns a fraction of a day into hours.
| Ticket | Clock hours | Business hours |
|---|---|---|
| HD-2236 | 8.58 | 7.83 |
| HD-2219 | 16.33 | 3.00 |
| HD-2230 | 28.50 | 13.00 |
Scenario: Enter the formula in E2 for HD-2236, logged 10/11/2026 08:15 and resolved 10/11/2026 16:50. Check that it returns 7.83 rather than the 8.58 the subtraction gave.
05Tickets Logged at Night or at the Weekend
A customer can log a ticket at any hour. The clock should start at the next opening, and the formula already does that.
HD-2224 was logged at 11:20 on a Saturday. NETWORKDAYS(B2,B2) is 0 on a weekend day, so NETWORKDAYS(B2,B2)*MOD(B2,1) is 0. MEDIAN lifts 0 to 09:00, and the day count from Saturday to Monday is 1, so no full days are added.
The ticket counts from 09:00 to 16:00 on Monday: 7 hours, not 52.67.
The same trick works at the other end. If a ticket is closed on a weekend, IF(NETWORKDAYS(C2,C2),...,$J$2) uses the closing time of the last working day, so the weekend adds nothing.
Scenario: Enter 07/11/2026 11:20 in B2 and 09/11/2026 16:00 in C2. Check that E2 returns 7, then change B2 to 04/11/2026 19:40 and C2 to 05/11/2026 12:00 and check that it returns 3.
06Bank Holidays Belong in All Three NETWORKDAYS
A closed day in the week is a holiday, and NETWORKDAYS only knows about it if you say so. Give every call the same holidays range, here a named range Holidays:
=((NETWORKDAYS(B2,C2,Holidays)-1)*($J$2-$J$1)
+IF(NETWORKDAYS(C2,C2,Holidays),MEDIAN(MOD(C2,1),$J$1,$J$2),$J$2)
-MEDIAN(NETWORKDAYS(B2,B2,Holidays)*MOD(B2,1),$J$1,$J$2))*24
A ticket logged at 16:00 on Thursday 24 December 2026 and fixed at 10:00 on Tuesday 29 December shows why. Christmas Day is a Friday, and the Boxing Day holiday moves to Monday 28 December.
| Holidays | Business hours |
|---|---|
| None listed | 19.5 |
| 25/12 and 28/12 listed | 2.5 |
Leaving Holidays out of only one of the three calls is worse than leaving it out of all of them. The pieces then disagree about which days exist, and a ticket logged on a holiday can come out negative.
Scenario: List 25/12/2026 and 28/12/2026 in a range named Holidays. Enter the ticket above and check that E2 returns 19.5 without the argument and 2.5 with it in all three places.
07Compare Against a Due Time, Not a Count
An hour count answers how long a ticket took. The SLA question is whether it was fixed by a deadline, and a deadline is a date-time you can show the customer.
Start the clock at the next business moment, add 8 business hours, and let WORKDAY skip the weekends. In F2:
=LET(open,$J$1, close,$J$2, span,close-open, sla,8/24,
st, IF(NETWORKDAYS(B2,B2)*(MOD(B2,1)<close),
INT(B2)+MAX(MOD(B2,1),open),
WORKDAY(INT(B2),1)+open),
k, MOD(st,1)-open+sla,
d, ROUNDUP(ROUND(k/span,6),0)-1,
WORKDAY(INT(st),d)+open+k-d*span)
st is the moment the clock starts. k is the time from that day's opening to the deadline, in office time, and d the number of whole working days it spills over.
HD-2207, logged at 16:45 on Friday, is due at 16:15 on Monday: 45 minutes on Friday and 7 hours 15 on Monday. HD-2224 is due at 17:00 on Monday.
The verdict is then a comparison. In H2:
=IF(C2<=F2,"Met","Breached")
ROUND(k/span,6)stops a binary tail such as 1.0000000001 from pushing a deadline that lands exactly on 17:30 to the next morning. HD-2230, logged at 09:30, is due at 17:30 the same day.
Scenario: Format F2 as ddd dd/mm/yyyy hh:mm and enter the formula for HD-2207. Check that it shows Mon 09/11/2026 16:15, then enter =IF(C2<=F2,"Met","Breached") in H2 and check that it returns Met.
08Check the Column Before the Report Goes Out
Two checks catch the faults this sheet had. Business hours can never be more than clock hours, so this must return 0:
=SUMPRODUCT(--(E2:E9>D2:D9))
Business hours can never be negative, so =MIN(E2:E9) must be 0 or more. A negative figure means a resolved time before its logged time, or a holidays range missing from one of the three calls.
Then count the verdicts. =COUNTIF(G2:G9,"Breached") returns 7 from the old column, and =COUNTIF(H2:H9,"Breached") returns 2 from the new one.
Scenario: Enter both checks above the ticket list. Then type a resolved time earlier than its logged time in one row, and check that =MIN(E2:E9) turns negative.
09Five Traps
- Subtracting timestamps for an SLA in business hours.
=(C2-B2)*24counts nights and weekends. Use theNETWORKDAYSformula or a due time. - Multiplying
NETWORKDAYSby the hours in a day.NETWORKDAYS(B2,C2)*8.5gives HD-2207 17 hours: two full days for 2 hours of work. - Typing times as text.
"16:45"pasted from a ticketing system is not a time, andMODreturns#VALUE!. Check with=ISNUMBER(B2). - Holidays in one call but not the others. All three
NETWORKDAYSneed the same range, or the pieces disagree. - Opening hours typed into the formula.
9/24and17.5/24buried in 800 rows go stale when the office changes its hours. Keep them in J1 and J2.
10Four Exercises
- Reproduce it. Enter the eight tickets and
=(C2-B2)*24, and confirm that seven of them exceed 8 hours. - Clamp it. Add
=MEDIAN(MOD(B2,1),$J$1,$J$2)and confirm that HD-2219's logged time becomes 17:30. - Count it. Add the full formula in column E and confirm that HD-2207 returns 2 and HD-2230 returns 13.
- Deadline it. Add the due time in column F and confirm that
=COUNTIF(H2:H9,"Breached")returns 2.
What to Take Away
The timestamps were right and so was the SLA. The report measured clock time against a promise made in office time, so every night and weekend counted against the desk.
Clamp each end to the working day with MEDIAN, count the days between with NETWORKDAYS, list the holidays in all three calls, and judge each ticket against a due time.