A month back, my colleague Mayank Sharma ( Heavy build – Super hero :)) asked me a question “How to calculate difference between two time slots in a standard time format”. So today I want to introduce a simple excel tip “How to calculate difference between two time slot”.
Let’s Say you want to find out how long an employee take to complete his/her project. There are many ways to calculate difference between time slots. One of them which I mostly use, Here it is.
1) To Calculate difference in standard time format.
Suppose a project Start Time is 6/11/2012 10:45 AM and End Time is 6/11/2012 5:30 PM.
Write Formula “=Text(B1-A1,”h:mm:ss”)” ( In this formula Start time is in Cell A1 and End time is in cell B1 of your excel worksheet)
2) To calculate result as a total of hours, minutes or seconds:
Example: Start time (Cell A3) = 6/11/2012 10:45 AM and End time (Cell B3) = 6/25/2012 5:30 PM
a). To get the total hours between the two times write this formula:
=INT((B1-A1)*24). The result is 342.
b). To get the total minutes between the two times write this formula:
=INT((B1-A1)*1440). The result is 20565.
c). To get the total seconds between the two times write this formula:
=INT((B1-A1)*86400). The result is 1233900.