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.
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:
To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (...
To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. In formula in cell I5 is: = ( F5 * H5 ) + ( G5 * H5 * 1.5 ) How this formula works Note: it's important to understand that Excel deals with time...
To calculate work hours, taking into account break time that needs to be subtracted, you can use a formula based on the MOD function. MOD is used to handle start and end times that cross midnight. In the example shown, the formula in F6 is: = MOD (...
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.