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*(C2A2)+(24*(D2B2))
Lets break this down:

C2A2 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)

D2B2 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.
Hi Mike,
Working with times is always tricky. But as you mention, once you appreciate 1 day has a value of 1 and Excel calculates using decimal time it’s a lot more understandable.
Your final formula can also be simplified to just E2 is =24*((C2A2)+(D2B2)) or just E2 is =24*(C2A2+D2B2). I don’t even bother separating dates and times now because you can subtract them directly if in a format like dd/mm/yyyy hh:mm. Just remember to multiply by 24 at the end!
You probably taught me this on one of your Excel courses years ago anyway ;).
Thanks… It’s working fine…Thanks
you can use =((D2B2)(D2<B2))*24
This will work in entire coloum with multiple shifts timing.
for any confusion mail me.
rajeshpeshiya0073@gmail.com
if the time is 12 am for example
time in is 2 pm and time out is 12 am
I need to add a column of overtime hrs to calculate amount of hours worked. How would I do this?
Hi I need help solving this problem. I need a formula that calculates End time based on the below start and finish times. Company opens 07:00 and is open 24 hours. Then shuts at 07:00 on the Saturday morning. I’m struggling to find a formula that can incorporate these times to be able to calculate an end date.
Example
Job Starts – 18/03/2019 07:00
Job Hours – 126
Job Finish – 25/03/2019 13:00
Any help appreciated
Shift Starts Ends
1st 6.00.00 13.59.59
2nd 14.00.00 21.59.59
3rd 22.00.00 05.59.59 (next day)
BD Start Date BD Start Time BD End Date BD End Time
01072019 6.30.00 AM 02072019 23.00.00 PM
How to calculate on 01.07.19 AShift : 7.30 Hrs., BShift : 8 Hr., CShift : 8 Hr.
on 02.07.19 AShift : 8 Hrs., BShift : 8 Hr., CShift : 1 Hr.