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. To calculate total hours between start and end time, the formula in D5 is: 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: If the start time is greater than end time (i.e. start time and end time cross midnight), we use: 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).
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
