In this post you will learn how to calculate elapsed time in Excel.
Calculating elapsed time –
One of the most common calculations done with time values is calculating elapsed time,
that is, how many hours and minutes between a start time and an end time.
The table in Figure below shows a list of start and end times, along with calculated elapsed
times. Look at the Figure,you can see that the formula in cell D4 is as follows:
=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 to subtract the
start time from the end time. There is a catch, however. 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.
In these cases, you have to add a 1 to the time to represent a full day. This ensures that
you don’t have negative elapsed times.
In our elapsed time formula, we use an IF function to check whether the end time is less
than the start time. If so, we add a 1 to our simple subtraction. If not, we can just do the
subtraction.
=IF(C4< B4, 1 + C4 - B4, C4 - B4)