One of the more common calculations done with time values involves calculating elapsed time — that is, the number of hours and minutes between a start time and an end time.
The table in the figure shows a list of start and end times along with calculated elapsed times. You can see that the formula in Cell D4 is
=IF(C4< B4, 1 + C4 - B4, C4 - B4)
To get the elapsed time between a start and end time, all you need to do is subtract the end time from the beginning time. However, there is a catch. If the end time is less than the start time, you have to assume that the clock has been running for a full 24-hour period, effectively looping back the clock.
For such cases, you have to add a 1 to the time to represent a full day. Adding a 1 ensures that you don’t have negative elapsed times.
In the elapsed time formula, you use an IF function to check whether the end time is less than the beginning time. If it is, you add a 1 to the simple subtraction. If it’s not, you just perform the subtraction:
=IF(C4< B4, 1 + C4 - B4, C4 - B4)
