The first thing most people do when they need to exclude weekends in Excel is wrap a date in WEEKDAY(A1) and filter or flag rows where it equals 1 or 7. That’s dangerously flawed — especially if your regional settings treat Monday as day 1 or Sunday as day 1, or if you’re working across international teams. Worse, it doesn’t handle holidays, ignores time zones in imported data, and breaks completely when dates include timestamps like 2024-06-15 14:32.
The Problem
You’re managing a sales pipeline in Sheet1, column A has lead creation dates, column B has names, and column C has deal values. Your manager asks: "How many leads came in on weekdays last month?" You try filtering manually — then realize 3 of the 12 entries are Saturdays. You try =IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday"), copy down, and get 4 mismatches. Why? Because your workbook uses 2010-era regional settings (Sunday = 1), but your colleague’s laptop defaults to Monday = 1. And three of those "weekend" dates? They’re actually Friday 11:59 PM — imported from Salesforce with timezone drift.
| A2:A12 (Date) | B2:B12 (Lead) | C2:C12 (Value) | Current WEEKDAY() Result | Actual Day | Should Exclude? |
|---|---|---|---|---|---|
| 2024-06-01 09:12 | Sarah Chen | $24,800 | 7 | Saturday | ✓ |
| 2024-06-02 16:45 | Acme Corp | $45,200 | 1 | Sunday | ✓ |
| 2024-06-03 08:22 | Nexus Labs | $12,900 | 2 | Monday | ✗ |
| 2024-06-07 23:59 | Vega Systems | $31,400 | 6 | Friday | ✗ |
| 2024-06-08 00:03 | TerraSoft | $8,600 | 7 | Saturday | ✓ |
| 2024-06-10 11:17 | Orion Group | $67,300 | 2 | Monday | ✗ |
| 2024-06-14 18:01 | Kairos Inc | $29,100 | 6 | Friday | ✗ |
| 2024-06-15 14:32 | Luma Dynamics | $15,500 | 7 | Saturday | ✓ |
| 2024-06-16 20:00 | Strata Holdings | $42,000 | 1 | Sunday | ✓ |
| 2024-06-17 07:44 | Brio Solutions | $19,800 | 2 | Monday | ✗ |
| 2024-06-22 13:28 | Zephyr Tech | $33,700 | 7 | Saturday | ✓ |
The Solution
Use NETWORKDAYS.INTL() — not for counting days between two dates, but as a logic gate to test single dates. Its second argument lets you define *exactly* which days are weekends — no regional guesswork. The beauty? It returns 1 for weekdays, 0 for weekends — clean, numeric, and stable.
- In cell D2, enter:
=NETWORKDAYS.INTL(A2,A2,"0000011"). That string means "Mon–Fri = workdays, Sat/Sun = off" — regardless of system locale. - Copy down to D12. You’ll see 1s beside Mon–Fri, 0s beside Sat/Sun.
- To filter weekday-only rows: Select A1:D12 → Alt + A + T → check "Select entire rows" → click OK. Then sort by column D (largest to smallest). Or use
=FILTER(A2:C12,D2:D12=1)in Excel 365.
What makes this elegant is how it handles edge cases: 2024-06-07 23:59 and 2024-06-08 00:03 both resolve correctly — because NETWORKDAYS.INTL truncates time automatically. No DATEVALUE() wrappers needed.
| A2:A12 (Date) | B2:B12 (Lead) | C2:C12 (Value) | D2:D12 (NETWORKDAYS.INTL) |
|---|---|---|---|
| 2024-06-01 09:12 | Sarah Chen | $24,800 | 0 |
| 2024-06-02 16:45 | Acme Corp | $45,200 | 0 |
| 2024-06-03 08:22 | Nexus Labs | $12,900 | 1 |
| 2024-06-07 23:59 | Vega Systems | $31,400 | 1 |
| 2024-06-08 00:03 | TerraSoft | $8,600 | 0 |
| 2024-06-10 11:17 | Orion Group | $67,300 | 1 |
| 2024-06-14 18:01 | Kairos Inc | $29,100 | 1 |
| 2024-06-15 14:32 | Luma Dynamics | $15,500 | 0 |
| 2024-06-16 20:00 | Strata Holdings | $42,000 | 0 |
| 2024-06-17 07:44 | Brio Solutions | $19,800 | 1 |
| 2024-06-22 13:28 | Zephyr Tech | $33,700 | 0 |
Going Further
Need to exclude holidays too? Add a holiday range. Say your holidays are listed in F2:F10 (e.g., 2024-07-04, 2024-12-25). Just extend the formula: =NETWORKDAYS.INTL(A2,A2,"0000011",F2:F10). Returns 0 if the date is *either* weekend or holiday.
Working with Middle East teams? Use "1000001" for Friday–Saturday weekend. For Japan, try "0111111" (only Sunday off). Full string options: each digit = Mon–Sun (1 = off, 0 = work).
Surprising tip: You can use this inside SUMIFS. To sum weekday deal values only: =SUMIFS(C2:C12,D2:D12,1). Or go dynamic: =SUMIFS(C2:C12,A2:A12,">="&DATE(2024,6,1),A2:A12,"<="&DATE(2024,6,30),D2:D12,1).
When NOT to Use This
Avoid NETWORKDAYS.INTL() if your source data contains invalid dates (like text "Jun 15" or blank cells) — it returns #VALUE! instead of 0. Wrap it: =IF(ISNUMBER(A2),NETWORKDAYS.INTL(A2,A2,"0000011"),0).
Don’t use it for time-based analysis within a day — e.g., "hours worked Monday–Friday 9 AM–5 PM". That needs MOD(), HOUR(), and custom logic.
If you’re on Excel 2007 or earlier, NETWORKDAYS.INTL() doesn’t exist. Fall back to =IF(OR(WEEKDAY(A2,2)>5),0,1) — but only after confirming your workbook uses return_type=2 (Mon=1, Sun=7) consistently. Test it first on known dates.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open AutoFilter | Ctrl + Shift + L | Works even if headers aren't selected |
| Reapply last filter | Alt + A + C | Great after editing filter criteria |
| Insert function dialog | Shift + F3 | Then type "networkdays.intl" and Tab |
| Toggle formula view | Ctrl + ` | Shows all formulas — helps debug weekend logic |
