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:
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.
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:
The complexity of calculating the number of hours between two times stems from times that cross midnight. This is because times that cross midnight often have a start time that is later than the end time (i.e. start at 9:00 PM, end at 6:00 AM). This...
Note: it's important to understand that Excel deals with time natively as fractions of a day. So, 12:00 PM is .5, 6:00 AM is .25, 6 PM is .75, and so on. This works fine for standard time and date calculations, but in many cases you'll want to...
At the core, this formula subtracts start time from end time to get duration in hours. This is done to calculate both work time and break time. MOD ( C6 - B6 , 1 ) // get work time MOD ( E6 - D6 , 1 ) // get break time Next, break time is subtracted...
The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.
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.