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:
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:
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.