01 / 11The Clock Ran Through Nights and Weekends

Working DaysNETWORKDAYSDate Functions

Business Hours Between Two Dates and Times in Excel

The Help Desk Reported Seven of Eight Tickets Breached and Only Two Were, Because the SLA Clock Kept Running Overnight and Through the Weekend

15 Nov 20269 min read

UsesWORKDAYMEDIANMODIF

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.

MeasureBreachedBreach rate
Clock hours, =(C2-B2)*247 of 887.5%
Business hours, 09:00–17:302 of 825.0%

Scope. NETWORKDAYS, WORKDAY, MEDIAN and MOD work in every version of Excel. The due-time formula in section 7 uses LET, which needs Excel 365 or 2021.

For weekends other than Saturday and Sunday, use NETWORKDAYS.INTL and WORKDAY.INTL in 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.

ABCDEFGH
1
Ticket
Logged
Resolved
Clock hours
Business hours
Due by
Status printed
Status true
2
HD-2201
Mon 02/11/2026 10:00
Mon 02/11/2026 15:30
5.5
5.5
Tue 03/11/2026 09:30
Met
Met
3
HD-2212
Tue 03/11/2026 15:00
Wed 04/11/2026 11:00
20
4.5
Wed 04/11/2026 14:30
Breached
Met
4
HD-2219
Wed 04/11/2026 19:40
Thu 05/11/2026 12:00
16.33
3
Thu 05/11/2026 17:00
Breached
Met
5
HD-2207
Fri 06/11/2026 16:45
Mon 09/11/2026 10:15
65.5
2
Mon 09/11/2026 16:15
Breached
Met
6
HD-2224
Sat 07/11/2026 11:20
Mon 09/11/2026 16:00
52.67
7
Mon 09/11/2026 17:00
Breached
Met
7
HD-2230
Mon 09/11/2026 09:30
Tue 10/11/2026 14:00
28.5
13
Mon 09/11/2026 17:30
Breached
Breached
8
HD-2236
Tue 10/11/2026 08:15
Tue 10/11/2026 16:50
8.58
7.83
Tue 10/11/2026 17:00
Breached
Met
9
HD-2241
Wed 11/11/2026 13:00
Thu 12/11/2026 13:30
24.5
9
Thu 12/11/2026 12:30
Breached
Breached

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.

TicketClock hoursBusiness hours
HD-220765.502.00
HD-222452.677.00
HD-221220.004.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.

Try it in the grid

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 dayClamped
08:1509:00
12:0012:00
19:4017: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.

Try it in the grid

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:

PieceValue
NETWORKDAYS Friday to Monday, minus 11 day, 8.5 h
Clamped resolved time10:15
Clamped logged time16:45
8.5 + 10.25 − 16.752.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.

Try it in the grid

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.

TicketClock hoursBusiness hours
HD-22368.587.83
HD-221916.333.00
HD-223028.5013.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.

Try it in the grid

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.

Try it in the grid

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.

HolidaysBusiness hours
None listed19.5
25/12 and 28/12 listed2.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.

Try it in the grid

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.

Try it in the grid

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.

Try it in the grid

09Five Traps

  1. Subtracting timestamps for an SLA in business hours. =(C2-B2)*24 counts nights and weekends. Use the NETWORKDAYS formula or a due time.
  2. Multiplying NETWORKDAYS by the hours in a day. NETWORKDAYS(B2,C2)*8.5 gives HD-2207 17 hours: two full days for 2 hours of work.
  3. Typing times as text. "16:45" pasted from a ticketing system is not a time, and MOD returns #VALUE!. Check with =ISNUMBER(B2).
  4. Holidays in one call but not the others. All three NETWORKDAYS need the same range, or the pieces disagree.
  5. Opening hours typed into the formula. 9/24 and 17.5/24 buried in 800 rows go stale when the office changes its hours. Keep them in J1 and J2.

10Four Exercises

  1. Reproduce it. Enter the eight tickets and =(C2-B2)*24, and confirm that seven of them exceed 8 hours.
  2. Clamp it. Add =MEDIAN(MOD(B2,1),$J$1,$J$2) and confirm that HD-2219's logged time becomes 17:30.
  3. Count it. Add the full formula in column E and confirm that HD-2207 returns 2 and HD-2230 returns 13.
  4. 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge