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:

Calculate Time Difference Where Period Exceeds 24 Hours

  • 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.