How to Calculate Business Hours in Excel (NETWORKDAYS, MOD & Where They Break)
If you've ever needed to know how many working hours (or business hours — same thing, different name) fall between two timestamps, you've probably run into NETWORKDAYS, then discovered it only counts whole days — and gone looking for a way to bolt on partial-day math. Here's how the formulas actually work, where they fall apart, and what to do instead.
Note: if what you actually need is a whole-day count rather than an hour-level figure, see our separate guide on counting business days in Excel — NETWORKDAYS and WORKDAY handle that case on their own, without any of the partial-day math below.
The starting point: NETWORKDAYS
Excel's built-in NETWORKDAYS(start_date, end_date, [holidays]) counts the whole working days between two dates, automatically excluding Saturdays and Sundays. It's a reasonable starting point if all you need is a day count:
The catch: it assumes a Monday–Friday week and counts in whole days, not hours. If your ticket opened at 4:45 PM and closed at 9:15 AM the next day, NETWORKDAYS tells you it spans "2 days" — not the roughly 40 minutes of actual business time involved.
Custom weekends with NETWORKDAYS.INTL
If your organization doesn't run a Monday–Friday week — common across the Middle East, where the weekend often falls on Friday–Saturday or Friday only — NETWORKDAYS.INTL lets you specify which days count as weekend using a numeric code:
The third argument is a weekend code (1 = Sat/Sun, 7 = Fri only, 11 = Sun only, and so on — Microsoft's documentation lists the full set). This solves the "which days are the weekend" problem, but it still doesn't solve the hours problem.
Where it actually gets hard: partial days and business hours
Once you need actual business hours — say, 9 AM to 5 PM — rather than whole days, the formula stops being one function and turns into a stack of nested IF, MOD, and MEDIAN logic. A commonly cited version looks something like this:
This kind of formula works — for the specific 9-to-5, Monday-to-Friday scenario it was written for. The moment your business hours change, your weekend days change, or you need to exclude holidays too, you're rebuilding it from scratch, and a typo in one MEDIAN or MOD reference silently gives you a wrong number with no error to flag it.
The edge cases that trip people up
- Shifts spanning midnight. If a shift starts at 10 PM and ends at 6 AM, most whole-day formulas don't correctly split the "before midnight" and "after midnight" business-hour portions.
- Holidays. NETWORKDAYS supports a holidays argument, but the MOD/MEDIAN partial-day formulas above usually don't — you'd need to add another layer of IF logic to skip holiday dates entirely.
- Daylight saving time. If your timestamps aren't timezone-aware, a DST transition can silently shift an hour in either direction.
- Weekend hours vs. business hours vs. total hours. Most formula stacks are built for exactly one of these metrics. Needing more than one means writing (and maintaining) more than one formula.
The faster way: skip the formula
If you find yourself rebuilding this formula for every new spreadsheet, it's usually faster to skip the formula entirely. Our Bulk Spreadsheet Calculator takes a .xlsx, .xls, .ods, or .csv file, lets you map your start/end columns, and appends Total Hours, Business Hours, Weekend Hours, and Weekday Hours as new columns — with the weekend days and business-hours window configurable per run, and no formula to maintain. Everything runs in your browser, so the file never leaves your computer.
If you just need one calculation rather than a whole spreadsheet, the Single Timespan Calculator does the same math for a single start and end date/time, instantly.
Frequently asked questions
Is "working hours" the same as "business hours" in Excel?
Yes. "Working hours," "business hours," and "office hours" are used interchangeably in most spreadsheets and formulas — there's no functional difference. All the NETWORKDAYS/MOD formulas above apply equally whichever term you use.
What's the difference between counting business days and calculating working hours?
Counting business days (NETWORKDAYS, WORKDAY) gives you a whole-number day count and ignores time entirely. Calculating working hours needs the partial-day math covered in this guide, because a shift or ticket rarely starts and ends exactly at midnight. See our guide to counting business days in Excel if a day count is actually all you need.