Most Excel trainers still teach you to build custom date loops or nest WEEKDAY functions to count workdays. They’re not just outdated—they’re dangerous. A single misplaced MOD(ROW(),7) can miscount by 3–5 days across a fiscal year. The truth? Excel has had a bulletproof, built-in solution since 1997—and it’s called NETWORKDAYS.
The Problem
You’re reconciling vendor delivery SLAs for Alibaba suppliers. Your team logs order dates (column A) and actual receipt dates (column B), but leadership insists on measuring performance in business days, not calendar days. Yet your current sheet uses simple subtraction: =B2-A2. That overstates urgency, inflates breach rates, and makes your logistics team look unreliable—even when shipments arrive Monday morning.
| Supplier | Order Date | Receipt Date | Calendar Days (A-B) | Actual Workdays (Manual Estimate) |
|---|---|---|---|---|
| Ningbo Precision Tools | 2024-04-01 | 2024-04-08 | 7 | 5 |
| Shenzhen OptoTech Ltd | 2024-05-10 | 2024-05-17 | 7 | 5 |
| Guangzhou FastPack Co. | 2024-06-24 | 2024-07-01 | 7 | 4 |
| Chengdu GreenFab Inc. | 2024-08-12 | 2024-08-19 | 7 | 5 |
| Xiamen SeaLogix | 2024-09-03 | 2024-09-10 | 7 | 5 |
| Hangzhou NanoShield | 2024-10-15 | 2024-10-22 | 7 | 4 |
| Dongguan SmartWeld Ltd | 2024-11-04 | 2024-11-11 | 7 | 5 |
See the mismatch? Rows 3 and 6 show 7 calendar days—but only 4 workdays because both periods include Chinese National Day (Oct 1–7) and Mid-Autumn Festival (Sep 17). Manual estimates are inconsistent. Worse: they’re impossible to audit. When Finance asks *“How did you get 4?”*, you have no formula trace—just a sticky note and hope.
The Solution
The fix is one function: NETWORKDAYS. It calculates workdays between two dates, excluding weekends (Saturday/Sunday) and optionally, a list of holidays. No arrays. No helper columns. No guesswork.
- In cell C2, type
=NETWORKDAYS(A2,B2)— assuming A2 holds 2024-04-01 and B2 holds 2024-04-08. - Press Enter. Result: 5.
- Select C2, then drag the fill handle down to C8. All values auto-calculate.
- To exclude Chinese holidays, first list them in column E: E2 = 2024-01-28 (Spring Festival start), E3 = 2024-02-04, E4 = 2024-04-04 (Qingming), E5 = 2024-05-01 (Labor Day), E6 = 2024-06-10 (Dragon Boat), E7 = 2024-09-17 (Mid-Autumn), E8 = 2024-10-01 (National Day), E9 = 2024-10-07.
- Update C2 to
=NETWORKDAYS(A2,B2,$E$2:$E$9). Note the absolute reference$E$2:$E$9— critical if you’re copying down.
What makes this elegant is how cleanly it handles edge cases: if A2 equals B2 and it’s a weekday, NETWORKDAYS returns 1 — not 0. That matches real-world SLA logic (“same-day processing counts as 1 business day”).
| Supplier | Order Date | Receipt Date | Workdays (NETWORKDAYS) |
|---|---|---|---|
| Ningbo Precision Tools | 2024-04-01 | 2024-04-08 | 5 |
| Shenzhen OptoTech Ltd | 2024-05-10 | 2024-05-17 | 5 |
| Guangzhou FastPack Co. | 2024-06-24 | 2024-07-01 | 4 |
| Chengdu GreenFab Inc. | 2024-08-12 | 2024-08-19 | 5 |
| Xiamen SeaLogix | 2024-09-03 | 2024-09-10 | 4 |
| Hangzhou NanoShield | 2024-10-15 | 2024-10-22 | 4 |
| Dongguan SmartWeld Ltd | 2024-11-04 | 2024-11-11 | 5 |
That last row? 2024-11-04 is a Monday, 2024-11-11 is a Monday — exactly 5 weekdays in between. Perfect.
Going Further
You’ll want these variations for real-world scenarios:
- Custom weekends? Use
NETWORKDAYS.INTL. For Middle East teams working Sunday–Thursday, replaceNETWORKDAYSwith=NETWORKDAYS.INTL(A2,B2,7,$E$2:$E$9). The “7” tells Excel Saturday & Sunday are off — change to “11” for Friday/Saturday weekend. - Count only Mondays and Wednesdays? Yes — but not with NETWORKDAYS. Use
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,3})). It’s heavy, but works. (Note: {1,3} = Mon=1, Wed=3 in WEEKDAY’s 2-based system.) - Include partial days? NETWORKDAYS doesn’t do time. If A2 = 2024-04-01 14:00 and B2 = 2024-04-02 10:00, it still returns 2 full workdays. To adjust, subtract 1 if same-day start/end falls outside core hours — but that’s a separate calculation layer.
- Holiday list from another sheet? Name your holiday range: select E2:E9 → Formulas tab → Define Name → “CN_Holidays”. Then use
=NETWORKDAYS(A2,B2,CN_Holidays). Cleaner, reusable, and immune to sheet-move errors.
Surprising tip: NETWORKDAYS treats both start and end dates as inclusive. So if A2 = 2024-01-01 (a holiday) and B2 = 2024-01-01, it returns 0 — not 1. That’s intentional and correct. But if your SLA says “order received Jan 1 counts as Day 1”, wrap it: =IF(A2=B2,IF(NETWORKDAYS(A2,A2,$E$2:$E$9)>0,1,0),NETWORKDAYS(A2,B2,$E$2:$E$9)).
When NOT to Use This
Don’t reach for NETWORKDAYS when:
- You need to count working hours, not days. Use
NETWORKDAYS+ time math, or better —NETWORKDAYS.INTL+MODfor fractional days. - Your organization observes floating holidays (e.g., “last Friday of June”) — NETWORKDAYS requires fixed dates. Build those dynamically with DATE formulas first, then feed them in.
- You’re comparing dates across time zones and daylight saving shifts. NETWORKDAYS operates purely on date serial numbers — it ignores time zones. Convert all timestamps to UTC before calculating.
- You’re auditing legacy data where weekends were sometimes worked. Then you need a log — not a formula. NETWORKDAYS assumes standard policy.
A hard limit: NETWORKDAYS fails silently if either date is text (e.g., “04/01/2024” typed as text, not a real date). Test with =ISNUMBER(A2). If FALSE, use =DATEVALUE(A2) to convert — or better, fix at the source with Data → Text to Columns → Date format.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Insert Function dialog | Shift+F3 | Type “networkdays” and double-click to insert with argument hints. |
| Open Formula Auditing | Alt+M+V | Shows precedents/dependents — critical when checking holiday range references. |
| Toggle absolute/relative refs | F4 | Press once on E2:E9 inside formula to cycle $E$2:$E$9 → E$2:E$9 → etc. |
| Quick date entry | Ctrl+; | Enters today’s date as a real number (not text) — safe for NETWORKDAYS. |
