Excel time difference in hours minutes seconds
Excel has a special way to treat Date Time value but most people are not aware of that. You need to understand the meaning of the numeric values that represent Excel Date Time in order that you can freely perform Excel date time conversion to hours, minutes, days, etc. After you understand the meaning of the numerical values, you can easily calculate Excel time difference.
I have written a post that explains conversion between time and numeric value, click here to read my previous post. I will also briefly recap the main concept below.
Excel Time mechanism
If you don’t specify a date when you enter a time in a Cell, you will see the default date is 0/1/2014 (Jan 0) if you change the Format to Date Time or Date.
To illustrate Excel Time, take 8:00 AM as an example. Type 8:00:00 in a Cell, then go to Format Cells, change Category to “General”.
Now the Cell display 0.33333, which is number of days (1/3 day), let’s take some more examples below,
For 0:00:00, day value is 0
For 24:00:00, day value i s 1
Excel time difference
In the below table, Cell A2 and B2 record the check in time of and employee, we try to find how many work hours are between check in time and check out time. Since we want to check the time difference between two time within the same day, we can just leave the date default as Jan 0, 1900.
|Check in time||Check out time|
|8:00:00 (A2)||10:30:00 (B2)|
To calculate the work hour, you can type =B2-A2, which returns 2:30:00 by default.
Since you are calculating the difference in hours, you don’t want to display in time format. Now use Format Cells to change the result to General in order to show the underlying numeric value. Now the result becomes 0.104167.
Since 0.104167 is a “day” unit, you can time this value by 24 in order to display hour.
The formula returns 2.5, which is hour unit
Since 1 hour is 60 minutes, 1 minute is 60 seconds, you can easily convert hours to minutes and seconds as below.
To convert to minute: =(A2-A1)*24*60 (150 minutes)
To convert to second: =(A2-A1)*24*60*60 (9000 seconds)
Excel Date Time difference
Use the example above except that I specify the date. This time we want an additional 31 days difference (from Jan 1 to Feb 1) on top of the 2.5 hours difference.
|Check in time||Check out time|
|1/1/2014 8:00:00 (A2)||1/2/2014 10:30:00 (B2)|
We can do exactly the same thing as above. Type =B2-A2, this time we got 2:30:00 again. This hour format does not reflect the day value, now try to convert to the numeric values.
Finally the result shows 31.1041667, which is exactly what we expected.
To convert to hour: =(A2-A1)*24 (746.5 hours)
To convert to minute: =(A2-A1)*24*60 (44790 minutes)
To convert to second: =(A2-A1)*24*60*60 (2687400 seconds)
Convert time difference to day+hours+minute
Take the above example 31.1041667 days. Now we want to show something like x Day y Hour z Minute, we will find x y z respectively.
To extract day x: =int(31.1041667) (return 31)
To extract hour y: =int((31.1041667-int(31.1041667))*24) (return 2)
To extract minute z: 31.1041667*24*60-int(31.1041667)*24*60-int((31.1041667-int(31.1041667))*24)*60 (return 30)