Explanation

This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours per day, 8 in the example shown.

The NETWORKDAYS function includes both the start and end date in the calculation, and excludes both Saturday and Sunday by default. The function will also exclude holidays when then are provided as the “holidays” argument as a range of valid dates.

In these example shown, the first two formulas use the NETWORKDAYS function.

D6=NETWORKDAYS(B6,C6)*8 // no holidays
D7=NETWORKDAYS(B7,C7,holidays)*8 // holidays provided

If your workweek includes days other than Monday through Friday, you can switch to the NETWORKDAYS.INTL function, which provides a “weekend” argument that can be used to define which days of the week are workdays and weekend days. NETWORKDAYS.INTL can be configured just like NETWORKDAYS, but it provides an additional argument called “weekend” to control which days in a week are considered workdays.

The next 4 formulas use the NETWORKDAYS.INTL function:

D8=NETWORKDAYS.INTL(B8,C8)*8 // Mon-Fri, no holidays
D9=NETWORKDAYS.INTL(B9,C9,11)*8 // Mon-Sat, no holidays
D10=NETWORKDAYS.INTL(B10,C10)*8 // M-F, no holidays
D11=NETWORKDAYS.INTL(B11,C11,1,holidays)*8 // M-F, w/ holidays

Custom work schedule

This formula assumes all working days have the same number of work hours. If you need to calculate work hours with a custom schedule where work hours vary according to the day of week, you can try a formula like this:

=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))

You can find an explanation here .

Explanation

This formula calculates total working hours between two dates and times, that occur between a “lower” and “upper” time. 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 the named ranges “lower” and “upper”.

The logic of the formula is to calculate all possible working hours between the start and end dates, inclusive, then back out any hours on the start date that occur between the start time and lower time, and any hours on the end date that occur between the end time and the upper time.

The NETWORKDAYS function handles the exclusion of weekends and holidays (when provided as a range of dates). You can switch to NETWORKDAYS.INTL if your schedule has non-standard working days.

Formatting output

The result is a number which represents total hours. Like all Excel times , you will need to format the output with a suitable number format . In the example shown, we are using:

[h]:mm

The square brackets stop Excel from rolling over when hours are greater than 24. In other words, they make it possible to display hours greater than 24. If you need a decimal value for hours, you can multiply the result by 24 and format as a regular number.

Simple version

If start and end times will always occur between lower and upper times, you can use a simpler version of this formula:

=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)

No start time and end time

To calculate total work hours between two dates, assuming all days are full workdays, you can use an even simpler formula:

=NETWORKDAYS(start,end,holidays)*hours

See explanation here for details.