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.
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*((C2-A2)+(D2-B2)) or just E2 is =24*(C2-A2+D2-B2). 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 =((D2-B2)-(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
01-07-2019 6.30.00 AM 02-07-2019 23.00.00 PM
How to calculate on 01.07.19 A-Shift : 7.30 Hrs., B-Shift : 8 Hr., C-Shift : 8 Hr.
on 02.07.19 A-Shift : 8 Hrs., B-Shift : 8 Hr., C-Shift : 1 Hr.