One of the most common calculations done with time values is calculating Elapsed time i.e the number of hours and minutes between start time and end time. Refer below data:

**How it works:**

Excel treats time serial numbers as numbers between 0 and 1 that represents fractions of a day.

Because they are just numbers, we can subtract one from another to get the difference between them: END TIME-START TIME

This expression works fine as long as END TIME is Greater than the START TIME. Now imagine a situation wherein a person works from 11:30 pm to 7:30 am, the expression 7:30 am-11:30pm will result in negative time value (Excel will display as series of # symbols)

To ensure that you get the correct positive result, we use the below formula:

**IF(EndTime<StartTime, 1+EndTime-StartTime,EndTime-StartTime)**

The IF() function checks whether EndTime is less than StartTime. If it is less than start time, it adds 1 to the value EndTime-StartTime to get the correct result;otherwise, just returns EndTime-StartTime.

Practice sheet for Elapsed Time (69 downloads)Hope you like this excel tip. Share with your friends and also make them awesome in excel.

## Leave a Reply