Exceljet

Quick, clean, and to the point

Total hours that fall between two times

Excel formula: Total hours that fall between two times
Generic formula 
=IF(start<end,MIN(end,upper)-MAX(start,lower),MAX(0,upper-start)+MAX(0,end-lower))
Explanation 

To calculate the total number of hours between a start and end time that overlap a period of interest with a given start and end time, you can use a formula based on the IF, MAX, and MIN functions. In the example shown, the formula in E5 is:

=IF(B5<C5,MAX(0,MIN(C5,upper)-MAX(B5,lower)),MAX(0,upper-B5)+MAX(0,C5-lower))

where "lower" is the named range I5, and "upper" is the named range I6.

Thanks to Robert Johnson for his fix to the original formula, which broke in certain cases. Current version should work when upper > lower.

How this formula works

In this example, we first calculate the total hours between the start time and end time. Then we figure out the total hours that overlap "the period of interest". Using these two values, we can figure out all remaining hours. In the example shown, "lower" is lower bound for the period of interest, and "upper" is the upper bound.

To calculate total hours between start and end time, the formula in D5 is:

=MOD(C5-B5,1)

This formula is explained in more detail here.

The formula in E5 works in two parts, using IF to control flow. If the start time is less than the end time, we calculate the overlap with:

MAX(0,MIN(C5,upper)-MAX(B5,lower))

If the start time is greater than end time (i.e. start time and end time cross midnight), we use:

MAX(0,upper-B5)+MAX(0,C5-lower)

By using MAX with zero, we prevent negative values from being used.

Finally, to figure out "Remaining" hours (i.e. hours that do not fall in the period of interest) we simply subtract E5 (included) from D5 (total duration).

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.