Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=((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

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

Result on the example
one result per row in D2, filled down to D7
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

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.

=((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

Result5.5

  1. B23/2/2026 10:00

    The value in B2.

  2. C23/2/2026 15:30

    The value in C2.

  3. NETWORKDAYS(B2,C2)1

    NETWORKDAYS: Weekdays between two dates, inclusive.

    • start_dateB23/2/2026 10:00
    • end_dateC23/2/2026 15:30
  4. NETWORKDAYS(B2,C2)-10

    The - operator subtracts.

  5. $G$25:00 PM

    The value in G2.

  6. $G$19:00 AM

    The value in G1.

  7. $G$2-$G$10.3333333333

    The - operator subtracts.

  8. NETWORKDAYS(B2,C2)-1)*($G$2-$G$10

    The * operator multiplies.

  9. NETWORKDAYS(C2,C2)1

    NETWORKDAYS: Weekdays between two dates, inclusive.

    • start_dateC23/2/2026 15:30
    • end_dateC23/2/2026 15:30
  10. MOD(C2,1)0.6458333333

    MOD: Remainder after division.

    • numberC23/2/2026 15:30
    • divisor1
  11. MEDIAN(MOD(C2,1),$G$1,$G$2)0.6458333333

    MEDIAN: Middle value of the numbers.

    • number1MOD(C2,1)0.6458333333
    • number2$G$19:00 AM
    • number2$G$25:00 PM
  12. IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$G$1,$G$2),$G$2)0.6458333333

    IF: Returns one value if a test is true, another if false.

    • logical_testNETWORKDAYS(C2,C2)1
    • value_if_trueMEDIAN(MOD(C2,1),$G$1,$G$2)0.6458333333
    • value_if_false$G$25:00 PMnot used
  13. NETWORKDAYS(B2,C2)-1)*($G$2-$G$1)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$G$1,$G$2),$G$2)0.6458333333

    The + operator adds.

  14. NETWORKDAYS(B2,B2)1

    NETWORKDAYS: Weekdays between two dates, inclusive.

    • start_dateB23/2/2026 10:00
    • end_dateB23/2/2026 10:00
  15. MOD(B2,1)0.4166666667

    MOD: Remainder after division.

    • numberB23/2/2026 10:00
    • divisor1
  16. NETWORKDAYS(B2,B2)*MOD(B2,1)0.4166666667

    The * operator multiplies.

  17. MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$G$1,$G$2)0.4166666667

    MEDIAN: Middle value of the numbers.

    • number1NETWORKDAYS(B2,B2)*MOD(B2,1)0.4166666667
    • number2$G$19:00 AM
    • number2$G$25:00 PM
  18. 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)0.2291666667

    The - operator subtracts.

  19. 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))*245.5

    The * operator multiplies.

In Google Sheets

The same formula works in Google Sheets: =((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. We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=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)5.50, 3.00, 3.00…The same with LET, which names the opening and closing times.
=(C2-B2)*245.50, 19.00, 67.00…Different resultElapsed 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate working hours between two dates and times

In D2, calculate the business hours from opened to resolved, counting only weekdays between the opening time in G1 and the closing time in G2. Fill down to D7.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.