Get work hours between dates and times

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower) where "lower" is the named range H5 and "upper" is the named range H6. Note: this example was inspired by a formula challenge on Chandoo, and a more complete solution provided by formula master Barry Houdini on the MrExcel forum.

April 14, 2025 · 1 min · 122 words · Beverly Miller

Table of Contents

In the example shown, the lower time is 9:00 AM and the upper time is 5:00 PM.

These appear in the formula as thenamed ranges"lower" and “upper”.

TheNETWORKDAYS functionhandles the exclusion of weekends and holidays (when provided as a range of dates).

Excel formula: Get workdays between dates

you might switch toNETWORKDAYS.INTLif your schedule has non-standard working days.

Formatting output

The result is a number which represents total hours.

Like allExcel times, you will need to format the output with a suitablenumber format.

Excel formula: Add business days to date

In other words, they make it possible to display hours greater than 24.

NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and canoptionallyexclude a list of holidays supplied as dates.

…

NETWORKDAYS.INTL Function

The Excel NETWORKDAYS.INTL function calculates the number of working days between two dates.

Excel formula: Get work hours between dates custom schedule

Excel formula: Get work hours between dates

Excel NETWORKDAYS function

Excel NETWORKDAYS.INTL function