In this blog post, we will see how to calculate Time difference in Excel. The methods include calculating Time difference with Overnight schedules.
Let’s say we have the ‘In Time’ and ‘Out Time’ of an Employee for a week.
Following are the different methods to calculate the difference between the In and Out times.
Table of Contents
Simple Subtraction for calculating Time difference
We can simply subtract In Time from Out Time. But here is what Excel returns when I subtract In Time from Out Time.
When 8:00 AM was subtracted from 5:30 PM, Excel returned 9:30.
Actually, there is nothing wrong with the calculation. Along with the arithmetic operation, Excel has copied the Number Formatting from the adjacent cell and that is why the cell is displaying 9:30 A.M instead of 9.30.
To remove the A.M from the result we have to apply proper Time Format to the corresponding cells. For that,
Select the cells containing formulas > Right-click > Format Cells…
In the Number tab of the Format Cells dialog > click on Custom under Category and select the following format
[h]:mm
Click OK and we have the Time difference in Proper format.
And if you want the Total of these Time differences, use SUM function.
But there can be times you need the Time difference in decimal hours. In that case you should use the following method.
Time difference in Decimal Hours
To calculate Time difference in Decimal Hours, use the following formula and apply Number Format to the corresponding cell.
=(End Time - Start Time)*24
The formulas explained above will work when the Out Time is greater than In Time.
Whenever, the In Time is greater than Out Time, the above formulas will result in negative time and will display a series of # sign.
So, while calculating Time difference and if negative time is expected as result, use any of the following formulas.
Formula using IF Function
Here, we will use the IF function to check whether the End Time is greater than Start Time or not.
=IF(End Time > Start Time , End Time - Start Time , End Time - Start Time + 1)
Means, if the End Time is greater than Start Time, Start Time is subtracted from End Time and the result is returned. Otherwise 1 is added to this result.
Let’s say we have the End Time in C5 and Start Time in B5, the formula becomes,
=IF(C5>B5,C5-B5,C5-B5+1)
Explanation of the formula
We have 10:45 PM (Start Time) in B5 and 8:30 AM (End Time) in C5. The above formula becomes,
=IF(8:30 AM>10:45 PM,8:30 AM-10:45 PM,8:30 AM-10:45 PM+1)
8:30 AM and 10:45 PM are converted into their number equivalents, 0.354166666666667 and 0.947916666666667 respectively.
=IF(0.354166666666667>0.947916666666667,0.354166666666667-0.947916666666667,0.354166666666667-0.947916666666667+1)
0.354166666666667 is less than 0.947916666666667.
So, the first argument of the IF function is evaluated to FALSE.
=IF(FALSE,-0.59375,-0.59375+1)
When the condition is evaluated as FALSE, function will return the third argument, -0.59375+1
= -0.59375+1
= 0.40625
= 9:45
0.40625 is equivalent to 9 hours 45 minutes.
Formula using MOD Function
The MOD Function in Excel can be used to find the remainder after division between two numbers.
This property of MOD function can be used to solve the negative problem while calculating Time difference.
Formula using the MOD function to calculate Time difference
=MOD(End Time - Start Time,1)
Let’s say we have the End Time in C5 and Start Time in B5. The formula becomes,
=MOD(C5-B5,1)
Related Tutorial