The Desk Breached 14 Tickets That Met Their Deadline and Passed the Three It Never Closed, Because NETWORKDAYS Counts Both End Dates and the Holiday List Stopped at 2025

The contract says five working days. The report that proves it is four columns wide: the date a ticket was logged, the date it was resolved, a count of working days between them, and a verdict. The count came from =NETWORKDAYS(B2,C2), which has been in the file since the contract was signed, and the verdict from =IF(D2<=5,"Met","Breached").

For August 2026 that report printed 31 breaches out of 145 tickets — 21.38% — against a contractual ceiling of 10%. At 7.5% of a £48,000 monthly fee, the service credit came to £3,600, and the account went into a formal remediation review with a weekly call attached to it.

Fourteen of those 31 tickets were resolved inside five working days.

Two things were wrong with the count and one thing was wrong with the verdict, and none of the three produced an error value anywhere on the sheet.

NETWORKDAYS counts working days, inclusive of both dates you hand it — not the gap between them. A ticket logged on Monday and resolved the following Monday has taken five working days and NETWORKDAYS returns 6. Nine tickets sat exactly on the line and every one of them printed as a breach.

The holidays argument was never supplied, so Monday 31 August — the summer bank holiday, a day on which nobody was at work and no ticket could be touched — counted as a working day for every ticket open across it. Five more tickets crossed from 5 to 6.

And three tickets had never been resolved at all. Their resolved cell was empty, NETWORKDAYS read an empty cell as zero, zero is 0 January 1900, and the function dutifully counted backwards from August 2026 to the beginning of the epoch: −33,032, −33,033 and −33,039. Every one of those is less than or equal to 5. The three tickets that had been open for 14, 13 and 7 working days — the only three in the month that nobody could defend — printed Met, and nothing escalated them, because a report that says a ticket met its SLA is a report that says the ticket is finished.

What this covers. NETWORKDAYS and WORKDAY are in every version of Excel on Windows, Mac and the web. NETWORKDAYS.INTL and WORKDAY.INTL, which take a weekend argument, arrived in Excel 2010 and are everywhere current. LET needs Excel 2021, 365 or the web; every formula below is also given without it. TODAY is volatile and recalculates on every edit, which matters in section 10. The worked example is a support desk because an SLA is the report where this survives longest: the number is small, it is recomputed monthly, and nobody re-derives it by hand.

1) Eight Tickets, Five Wrong Verdicts, No Error Values

Here are eight of the 145 rows, with what the report counted beside what was actually worked.

Read the last two columns against each other. INC-4402, opened and closed inside one afternoon, is reported as having taken a day — harmless here, and the same off-by-one that flipped the next three rows. INC-4418 and INC-4423 are the clean Monday-to-Monday case: five working days worked, six counted. INC-4441 and INC-4437 are five days worked and seven counted, because they were open across 31 August and the count both included the bank holiday and added its own extra day. INC-4460 is a real breach, correctly flagged, and it is the only row on this table where the report and the truth agree for the right reason. The last two rows are the open tickets, printing a negative five-figure number into a column headed Working days, and passing.

Nothing on the sheet is red. There is no #VALUE!, no #NUM!, no blank in the verdict column. Every count is an integer, most of them are plausible, and the two that are not plausible sit in a column nobody reads once the verdict column exists.

🎯 Scenario: Open any file that ages something — tickets, invoices, claims, approvals — and find the cell that counts the days. If it is a bare =NETWORKDAYS(start,end) with two arguments and no third, you have both of this report's faults, and the only question left is whether anything in your data has ever landed exactly on the limit.

2) NETWORKDAYS Counts Days, Not the Gap Between Them

=NETWORKDAYS(start, end, [holidays]) returns the number of whole working days in the closed interval from start to end. Both endpoints are included. Weekends are excluded, holidays are excluded if you pass them, and that is the whole function.

The consequence is one cell wide:

=NETWORKDAYS(DATE(2026,8,5), DATE(2026,8,5))   → 1

One working day, for a ticket raised at 09:40 and closed at 15:10 on the same Wednesday. Nothing elapsed. The function is not wrong — it is answering how many working days does this interval contain, and the answer is one, the Wednesday. The report was asking how long did this take, and the answer to that is zero.

So the two measures differ by one whenever both endpoints are working days:

The -1 is correct only while the start date is itself a working day. Log a ticket at 22:00 on a Saturday and NETWORKDAYS counts from the Monday, so subtracting one now understates by a day. That is not an argument for a cleverer subtraction; it is the reason section 5 stops counting altogether and compares against a due date instead.

🎯 Scenario: Put =NETWORKDAYS(A1,A1) in an empty cell with any working date in A1. It returns 1, and that single cell is the entire off-by-one, demonstrated in a form you can show somebody in ten seconds. Change A1 to a Saturday and it returns 0.

3) The Holidays Argument Is Optional in the Syntax and Compulsory in Practice

The third argument is a date, a range of dates or an array of them, and every date in it that falls on a weekday inside the interval is removed from the count.

=NETWORKDAYS(DATE(2026,8,24), DATE(2026,9,1))              → 7
=NETWORKDAYS(DATE(2026,8,24), DATE(2026,9,1), Holidays)    → 6

Leave it out and Excel does not warn you, because there is nothing to warn about: a function given no holidays correctly reports that no holidays were excluded. The workbook has no opinion about which country you are in, which office observes what, or whether the list it was handed in 2024 is still true.

This one was still true, until it was not. The named range Holidays existed, on a tab called Ref, and it ran from 01/01/2024 to 26/12/2025 — two years typed out by somebody who has since left, and never extended. It was also not passed to the formula, which is the more embarrassing half of the story and the half that made the missing rows invisible: even a current list cannot help a formula that does not reference it.

Four things about holiday lists that are worth knowing before you build one:

• A holiday that falls on a weekend subtracts nothing. 26 December 2026 is a Saturday. Putting 26/12/2026 in the list changes no count anywhere, because that day was never counted in the first place. What the UK actually closes for is the substitute day, Monday 28 December 2026, and that is the date the list needs.
• Text that looks like a date breaks the whole formula, loudly. Paste a holiday list from a web page and you often get left-aligned text rather than dates. NETWORKDAYS returns #VALUE! for the entire column rather than quietly ignoring the bad entry — the one failure in this family that announces itself.
• Duplicates are harmless. The same date twice in the list is removed once. You do not need to deduplicate before you use it.
• Blanks are harmless too. An over-sized range — Ref!$A$2:$A$500 against 60 real holidays — reads the empty cells as zero, and serial 0 is a date in January 1900 that falls outside any interval you will ever measure. Size the range for the next five years and stop thinking about it.

🎯 Scenario: In an empty cell, =TEXT(MAX(Holidays),"dd/mm/yyyy"). That is the last day your workbook believes is a holiday. If it is in the past, every count that crosses a holiday since then is one day too high, and the report has been drifting for exactly as long as the list has been stale.

4) The Blank Resolved Date That Printed "Met" at −33,032

An empty cell handed to a date function is not an error and not a blank. It is zero, and zero is a date: 0 January 1900, the day before Excel's epoch begins.

So an unresolved ticket asks NETWORKDAYS to count working days from 11 August 2026 back to the start of the calendar, and the function does it, correctly, in both directions:

=NETWORKDAYS(B2, C2)     ' B2 = 11/08/2026, C2 empty   → -33032
=IF(D2<=5, "Met", "Breached")                        → "Met"

There is no rule in Excel that a duration must be positive, and there is no rule in a <= comparison that says a large negative number is suspicious. The verdict column was built to catch numbers that were too big. Nothing in it was built to catch a number that was too small, because until a resolved date went missing, too small was not a thing that could happen.

This is the failure that cost more than the £2,400. The three tickets were the three oldest in the book, and the report that everybody used to decide what to chase said they were done.

Two guards, either of which is enough:

=IF(C2="", "Open", IF(NETWORKDAYS(B2,C2,Holidays)-1<=5, "Met", "Breached"))
=IF(C2="", "", NETWORKDAYS(B2,C2,Holidays)-1)

Test the input, not the output. A blank is a fact about the ticket — it is not finished — and a report that folds it into a number has thrown that fact away before any verdict is reached.

🎯 Scenario: =COUNTBLANK(C2:C146) over your end-date column, and =SUMPRODUCT(--(D2:D146<0)) over your duration column. The first counts the rows that cannot be measured yet; the second counts the rows that were measured anyway. Both should be zero, and on this sheet the second was 3.

5) WORKDAY Is the Other Half of the Pair, and It Does Not Count the Start Day

=WORKDAY(start, days, [holidays]) goes the other way: hand it a date and a number of working days and it returns the date that many working days later, skipping weekends and holidays.

=WORKDAY(DATE(2026,8,3), 5, Holidays)    → 10/08/2026
=WORKDAY(DATE(2026,8,24), 5, Holidays)   → 01/09/2026

WORKDAY counts forward from the start date without counting it, which is exactly the measure the SLA means and exactly the measure NETWORKDAYS does not give you. The two functions are consistent with each other — NETWORKDAYS(start, WORKDAY(start,5)) is 6 — and inconsistent with each other's intuition, which is the whole reason the off-by-one survives in so many files.

That asymmetry is a gift, because it means you never have to reason about the counting rule again. Compute the deadline once, compare the resolution to it, and the question does the start day count? disappears into a function that has already answered it:

=WORKDAY(B2, 5, Holidays)                              → the due date
=IF(C2="", "Open", IF(C2<=D2, "Met", "Breached"))      → the verdict

Two more things it does:

• It handles a start date that is not a working day. A ticket logged at 22:00 on Saturday 1 August gets WORKDAY(01/08/2026, 5) = Friday 7 August, the clock effectively starting Monday. No special case, no IF.
• It walks backwards on a negative number. =WORKDAY(EOMONTH(TODAY(),0)+1, -3, Holidays) is the third-to-last working day of this month — the shape of every submission cut-off, payment run and close deadline anybody has ever had to publish.

WORKDAY returns a serial number, so a cell formatted as General shows 46244 rather than a date. Format the column as a date once and it never comes up again.

🎯 Scenario: Add one column to your ageing report — =WORKDAY(logged, <your SLA>, Holidays) — and sort by it. You now have a queue in the order the deadlines actually fall, which is not the order the logged dates fall, because a ticket logged on a Friday afternoon is due before one logged the following Monday morning even though it sat in the queue longer.

6) NETWORKDAYS.INTL and WORKDAY.INTL: When the Week Is Not Monday to Friday

Both functions have an .INTL sibling with one extra argument in third position, before holidays, that says which days are the weekend.

=NETWORKDAYS.INTL(start, end, [weekend], [holidays])
=WORKDAY.INTL(start, days, [weekend], [holidays])

The weekend argument takes a code or a string:

The string form is seven characters, Monday first, 1 for a non-working day:

=NETWORKDAYS.INTL(B2, C2, "0000110", Holidays)   ' Fri + Sat off
=NETWORKDAYS.INTL(B2, C2, "0000011", Holidays)   ' the ordinary Sat + Sun, written out
=NETWORKDAYS.INTL(B2, C2, "0001011", Holidays)   ' a four-day week, Thursdays off
=NETWORKDAYS.INTL(B2, C2, "0000001", Holidays)   ' a six-day week, Sunday only

There is no string with seven 1s — a week with no working days in it returns #VALUE! rather than dividing your calendar by zero.

The string is the one to reach for, because it covers the cases the codes cannot: a single non-working day mid-week, a plant that shuts on Wednesdays, a part-time rota. And it is the same seven characters in every language version of Excel, which a code is not guaranteed to be readable as.

🎯 Scenario: If any part of your operation works a week other than Monday to Friday, put the weekend string in a cell next to the holiday list, not inside the formula. =NETWORKDAYS.INTL(B2,C2,$F$1,Holidays) is one edit when the rota changes; the same string typed into 400 formulas is a find-and-replace and a prayer.

7) Times Are Not Days, and Most SLAs Are Really Hours

NETWORKDAYS drops the time part of both arguments. A ticket logged at 16:55 on Monday and closed at 09:05 on Friday, and one logged at 09:05 on Monday and closed at 16:55 on Friday, return the same number, and the second took two full working days more than the first.

If your timestamps carry times — and an exported ticket log almost always does — you are measuring calendar boundaries crossed, not work done. That is a legitimate measure, and it is the measure most contracts are written in, but it is worth knowing which one you have.

When the SLA is genuinely in hours, count the hours:

=LET(
  st, B2, en, C2, dayhrs, 7.5, opens, 9/24,
  full,  NETWORKDAYS(st, en, Holidays) - 2,
  first, MEDIAN(0, (opens + dayhrs/24 - MOD(st,1)) * 24, dayhrs),
  last,  MEDIAN(0, (MOD(en,1) - opens) * 24, dayhrs),
  full*dayhrs + first + last
)

Without LET, the same thing in three helper columns — working days between, hours left on the first day, hours used on the last — is clearer to audit and easier to explain to the person who queries a number. Either way, the rule is that the unit in the formula matches the unit in the contract. A five-day SLA measured in days and a 37.5-hour SLA measured in hours are different reports that will disagree on exactly the tickets people argue about.

🎯 Scenario: =SUMPRODUCT(--(MOD(B2:B146,1)>0)) over your start column. If it is not zero, your dates carry times, and every whole-day count in the file is rounding somebody's day up or down without saying so.

8) One Holiday List Per Calendar, Not One Per Workbook

A desk covering a UK client and an Irish one has two calendars. They agree on Christmas and differ on the first Monday in June, and a single Holidays range quietly applies one country's days off to the other's tickets.

Keep the holidays in a two-column Table — Date and Calendar — and select the right ones per row:

=NETWORKDAYS(B2, C2, FILTER(Hols[Date], Hols[Calendar]=$D2))

The holidays argument takes an array, so a spilled FILTER goes straight in with nothing wrapped around it. Before FILTER — Excel 2019 and earlier — keep one column per calendar side by side and point at the right one with INDEX/MATCH on the header row, or simply keep two named ranges and choose with IF. What you should not do is keep two workbooks.

🎯 Scenario: =COUNTA(Hols[Date])-COUNT(Hols[Date]) returns the number of entries in your holiday table that are text rather than dates. It should be zero. Any other answer is the #VALUE! in section 3 waiting for the row it applies to.

9) A Holiday Table That Cannot Go Stale Quietly

The list that broke this report did not break. It ended. Nothing in Excel treats the end of a list as an event, so build the check into the sheet:

=IF(MAX(Hols[Date]) < EOMONTH(TODAY(),3),
    "HOLIDAY LIST ENDS " & TEXT(MAX(Hols[Date]),"dd/mm/yyyy") & " — EXTEND IT",
    "")

Put that in a cell at the top of the report, in red, where the person who runs the report has to look at it. Three months of runway is enough to fix it calmly and short enough that nobody ignores it for a year.

The fixed-date holidays can be generated rather than typed, including the substitute-day rule that catches people out:

=DATE(A2,12,25)                                  ' Christmas Day, whatever weekday it lands on
=WORKDAY(DATE(A2,12,25)-1, 1)                    ' the day it is actually observed
=DATE(A2,5,1) + MOD(8-WEEKDAY(DATE(A2,5,1),2),7)     ' first Monday in May
=DATE(A2,6,1) - MOD(WEEKDAY(DATE(A2,6,1),2)-2,7) - 1  ' last Monday in May
=DATE(A2,9,1) - MOD(WEEKDAY(DATE(A2,9,1),2)-2,7) - 1  ' last Monday in August

Easter is not a one-liner anybody should trust, and the holidays that move with it — Good Friday on 3 April 2026, Easter Monday on the 6th — get typed in. So does anything discretionary: the day between Christmas and New Year that your office closes and the statutory calendar does not.

🎯 Scenario: Put the year in a column and the generated dates beside it, five years deep, and compare them against your government's published list once. After that the only rows anybody maintains by hand are Easter and the company's own closures — about four cells a year.

10) The Report, Rebuilt in Four Columns

D2:  =WORKDAY(B2, 5, Hols[Date])                                   ' due
E2:  =IF(C2="", IF(TODAY()>D2, "Breached — still open", "Open"),
        IF(C2<=D2, "Met", "Breached"))                             ' verdict
F2:  =IF(C2="", "", NETWORKDAYS(B2, C2, Hols[Date]) - 1)           ' working days taken
G2:  =IF(C2="", MAX(0, NETWORKDAYS(D2,TODAY(),Hols[Date]) - 1), "") ' working days overdue

Or in one cell, if you have LET:

=LET(
  logged, B2, resolved, C2,
  due, WORKDAY(logged, 5, Hols[Date]),
  IF(resolved="",
     IF(TODAY()>due, "Breached — open " & NETWORKDAYS(due,TODAY(),Hols[Date])-1 & "d", "Open"),
     IF(resolved<=due, "Met", "Breached by " & NETWORKDAYS(due,resolved,Hols[Date])-1 & "d"))
)

What changed is not the arithmetic. It is that the verdict now comes from a date comparison rather than a count, so the counting rule cannot be got wrong; the deadline is computed by the function whose job is deadlines; the holidays are named in every formula that touches a calendar; and an unresolved ticket is a state the report understands rather than an empty cell it silently converts to 1900.

Run that against August and it returns 20 breaches, 3 of them still open, and a 13.79% rate.

🎯 Scenario: Rebuild one month you have already reported, in a copy, with the due-date version. If the two disagree, the disagreement is a list of every row that was ever argued about, and it is usually short enough to check by hand and long enough to be worth the afternoon.

11) Five One-Cell Checks

1. Is the counting inclusive? =NETWORKDAYS(A1,A1) with a working date in A1. Returns 1. If your report treats that as a day taken, every duration in it is one too many.
2. Has the holiday list run out? =TEXT(MAX(Hols[Date]),"dd/mm/yyyy"). Compare to the last date in your data. This one read 26/12/2025 against an August 2026 report.
3. Is anything being measured backwards? =SUMPRODUCT(--(NETWORKDAYS(B2:B146,C2:C146,Hols[Date])<0)). Counts rows where the end date precedes the start, blanks included. Was 3, should be 0.
4. Do the holidays reach the formulas? =NETWORKDAYS(DATE(2026,8,28),DATE(2026,9,1),Hols[Date]). Returns 2 if the summer bank holiday is in the list and reaching the function, 3 if it is not. Any date you know is a holiday works.
5. Do two routes agree? =COUNTIF(E2:E146,"Breached") against =SUMPRODUCT(--(C2:C146>D2:D146)*(C2:C146<>"")). The verdict column and the raw comparison should give the same number. When they do not, the IF chain has a case in it that nobody designed.

12) Twelve Traps

1. NETWORKDAYS includes both endpoints. Same-day is 1, not 0, and Monday to the next Monday is 6, not 5. Subtract one, or stop counting and compare against a WORKDAY deadline.
2. WORKDAY excludes the start day. The pair are not symmetrical, which is why mixing them in one report produces two definitions of "five days" in adjacent columns.
3. The holidays argument is optional and its absence is invisible. No error, no flag, no colour — just counts that are high by exactly the number of public holidays your data spans.
4. A holiday list has a last row. It does not expire, warn or fail; it simply stops being true, and the report keeps printing.
5. A holiday on a weekend does nothing. Excel already skipped it. The substitute weekday is the date that needs to be in the list.
6. A blank end date is 0 January 1900, so an unfinished item returns a five-figure negative number and passes every "under the limit" test in the file.
7. Text dates return #VALUE! for the whole formula, including one text entry buried in an otherwise clean holiday range.
8. Times are silently dropped. 16:55 Monday to 09:05 Friday and 09:05 Monday to 16:55 Friday count identically.
9. The weekend argument sits in third position in .INTL, where holidays sit in the plain version. =NETWORKDAYS.INTL(B2,C2,Hols) reads your holiday range as a weekend code.
10. A weekend string is seven characters starting Monday, and "0000011" is the ordinary weekend. Getting it backwards produces a working week of Saturday and Sunday, which counts about 40% of what you expected and never errors.
11. TODAY() is volatile. A report of days-overdue recalculates every time the file is opened, so last month's saved copy does not say what it said when it was sent. Paste the ageing columns as values before archiving.
12. WORKDAY returns a serial number. An unformatted due-date column of 46244s is the one trap in this list that you notice immediately, which makes it the least expensive.

Nobody in this story did anything unreasonable. =NETWORKDAYS(B2,C2) is what the function browser offers when you type working days, the two-argument form is the one every tutorial shows first, and a count of days is the obvious way to answer a question about a number of days. The holiday list was built by somebody thorough enough to build a holiday list. The verdict column was written to catch tickets that took too long, and it caught them.

What made it expensive is that all three faults push the number in a direction that looks like bad news. An inclusive count reads high. A missing bank holiday reads high. A report that says your team is failing more than it is gets escalated, investigated and paid for — and every one of those responses treats the number as a floor rather than a question. The one fault that pushed the other way was invisible by construction, because the tickets it hid were the ones reported as finished, and nobody audits the rows that passed.

So the discipline is small, and it is three habits. Compute the deadline with WORKDAY rather than counting days with NETWORKDAYS, so the function that knows the counting rule is the one applying it. Name a holiday range, pass it to every formula that touches a calendar, and put a cell at the top of the report that goes red three months before the list runs out. And test the inputs before the verdict — a blank end date is a state, not a zero, and the moment a report is allowed to turn unfinished into a number, the worst rows in the file are the ones it will hide.