Calculating time difference between Start time and End time

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.


Be the first to comment

Leave a Reply

Your email address will not be published.