How to Subtract Time in Excel and Calculate Time Difference

One of the most complex operations with time values is calculating the time interval, meaning the number of hours and minutes between the start and end time values.



How to Subtract Time from Today and Tomorrow in Excel

The table in the image below contains a list of start and end time values in its cells, as well as the results of calculating the time interval:

As shown in the image, simply subtracting the values results in an incorrect format for some results, displaying ###:

Therefore, when calculating time intervals to determine the time difference, you should use a specific formula in Excel. The formula looks like this:

=IF(B2<A2,B2-A2+1,B2-A2)

Download the example on how to calculate the time difference in Excel

To calculate the time interval between the start and end times, it's not enough to simply subtract the start time from the end time. There's a common trap that Excel users often fall into. If the end time is earlier than the start time, you should account for the fact that the clock has moved into the next 24-hour day.

In such cases, you should add one to the end time, which in Excel's time format represents a full day. This ensures that the calculated time intervals do not result in negative values.

The formula uses the logical IF function to calculate the time interval. It first checks if the end time is earlier than the start time. If it is, the result of the subtraction is increased by one (24 hours). Otherwise, only the subtraction is performed.