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.

Explanation

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7.

The trick to this formula is assembling an array of dates that you can feed into the WEEKDAY function. This is done with ROW with INDIRECT:

ROW(INDIRECT(B6&":"&C6))

ROW interprets the concatenated dates as row numbers and returns an array like this:

{43346;43347;43348;43349;43350;43351;43352}

Each number in the array represents a date. The WEEKDAY function then evaluates the array and returns an array of weekday values:

{2;3;4;5;6;7;1}

These numbers correspond to the day of week of each date. They are provided to the MID function as the start number argument, along with the value in D6, “0888884” for text:

MID("0888884",{2;3;4;5;6;7;1},1)

Because we are giving MID an array of start numbers, it returns an array of results like this:

{"8";"8";"8";"8";"8";"4";"0"}

These values correspond to the hours worked on each day from the start date to the end date. Note the values in this array are text, not numbers. To convert to actual numbers, we multiply by a second array created to manage holidays, as explained below. The math operation coerces the text to numeric values.

Holidays

To handle holidays, we use ISNA, MATCH, and the named range “holidays” like this:

ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))

This expression uses MATCH to locate dates that are in the named range holidays using the same array of dates generated above with INDIRECT and ROW. MATCH returns a number when holidays are found and the #N/A error when not. The ISNA function “flips” the results so that TRUE represents holidays and FALSE represents non-holidays. ISNA returns an array or results like this:

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

Finally, both arrays are multiplied by each other inside SUMPRODUCT. The math operation coerces TRUE and FALSE to 1 and zero, and the text values in the first array to numeric values (as explained above), so in the end we have:

=SUMPRODUCT({8;8;8;8;8;4;0}*{0;1;1;1;1;1;1})

After multiplication, we have a single array inside SUMPRODUCT containing all working hours in the date range:

=SUMPRODUCT({0;8;8;8;8;4;0})

SUMPRODUCT then sums all items in the array and returns a result of 36.