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.