# How to calculate working hours between two dates and times in Excel

Count full working days with NETWORKDAYS and trim the first and last day to opening hours with MEDIAN.

**Excel:** `=((NETWORKDAYS(B2,C2)-1)*($G$2-$G$1)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$G$1,$G$2),$G$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$G$1,$G$2))*24` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Ticket | Opened | Resolved | Business hours |  | Opens | 0.375 |
| T-801 | 46083.416666666664 | 46083.645833333336 |  |  | Closes | 0.7083333333333334 |
| T-802 | 46083.666666666664 | 46084.458333333336 |  |  |  |  |
| T-803 | 46087.625 | 46090.416666666664 |  |  |  |  |
| T-804 | 46084.333333333336 | 46084.770833333336 |  |  |  |  |
| T-805 | 46088.5 | 46090.395833333336 |  |  |  |  |
| T-806 | 46085.541666666664 | 46087.541666666664 |  |  |  |  |

Formula in D2, filled to D7: `=((NETWORKDAYS(B2,C2)-1)*($G$2-$G$1)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$G$1,$G$2),$G$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$G$1,$G$2))*24`

Result: 5.50; 3.00; 3.00; 8.00; 0.50; 16.00

## How it works

Every working day from the first to the last counts as a full business day: `(NETWORKDAYS(B2,C2)-1)` times the 8 open hours. The ends are then trimmed: MEDIAN clamps the resolved time and the opened time between opening and closing, so time before 9:00 or after 17:00 doesn't count. T-803 was opened Friday at 3:00 PM and resolved Monday at 10:00 AM: 2 hours plus 1 hour.

## Other ways

- `=LET(open,$G$1,close,$G$2,((NETWORKDAYS(B2,C2)-1)*(close-open)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),open,close),close)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),open,close))*24)`: The same with LET, which names the opening and closing times.
- `=(C2-B2)*24`: Elapsed clock hours, including nights and weekends. (Counts every hour.)

## Common mistakes

- It assumes the same opening hours every working day. Different hours on some days need a lookup per day.
- To skip holidays, add the holiday range as the third argument of all three NETWORKDAYS.

## Questions

**How do I skip holidays too?**

Add the range to each NETWORKDAYS, for example `NETWORKDAYS(B2,C2,$I$2:$I$12)`.

**How do I show the result as hours and minutes?**

Leave out *24 and use the custom format [h]:mm.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/working-hours-between-two-dates-and-times