I was recently asked how to calculate the difference between two times where the time period exceeds 24 hours. For example: 08:00 on 1st March to 14:00 on 2nd March (which is 30 hours).
In the screenshot below:
- A2: 1st March 2015
- B2: 08:00
- C2: 2nd March 2015
- D2: 14:00
The formula in E2 is =24*(C2-A2)+(24*(D2-B2))
Lets break this down:
C2-A2 calculates the number of days between the "Start Date" and the "End Date". In this example it is 1 (2nd March – 1st March)
The result of that is multiplied by 24 (there are 24 hours in a day). So in this example we now have the value 24
For calculation purposes the way that Excel handles time is that a 24 hour period is assigned the value 1
Therefore an hour is equal to 1/24 (which to Excel is 0.04167 as Excel works with decimals and not fractions)
D2-B2 calculates the number of hours between the "Start Time" and "End Time". The result of this is 0.25
If you multiply 0.25 by 24 you will get 6
Finally add up the 24 and the 6 to get 30
This should work for any combination of dates and times.