Calculating total hours between two dates

SNA400

Board Regular
Joined
Nov 5, 2010
Messages
51
I need to count the number of hours between 2 dates. example I need the difference between 21/04/2014 00:10 and 23/04/2014 23:50 (answer is 47h 40 mins)

The cells have a start/finish date/time in the format dd/mm/yyyy hh:mm

When I minus the start date/time from the finish date/time I want the result to be number of total hours.

I have tried SUM(A1-B1) but this only calculates the difference between the two hour totals

I have also tried the formula below
=NETWORKDAYS(D4,E4)+NETWORKDAYS(E4,E4)*(MOD(D4,1)-1)-NETWORKDAYS(D4,D4)*MOD(D4,1)
but this only shows me the time entry in D4

I an sure there is a simple answer to this question - but I can't work it out!

Simon
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Steve's formula should work? But from your example, the difference between both times isn't 47hours, its about 72hours except am not doing something correctly
 
Upvote 0
Sorry guys

Dont think I made myself clear - I need to calculate the difference in total hours between when the container is booked for and when it was tipped and also the difference from arrival to when tipped

A portion of the sheet I have is below - the third container in the list on the arrival to tip is using =SUM(E4-D4)*24 - and the calculation should be around 10 hours - not 22.
the container calculation for arrival to tip on the first row should be around 53 hours - not the 4:57 shown

So you can see my dilemma! all the dates and times are all over the place so I need a simple way of getting the difference.

[TABLE="width: 855"]
<tbody>[TR]
[TD="class: xl65, width: 121, bgcolor: #FFFF83"]Container No.
[/TD]
[TD="class: xl65, width: 105, bgcolor: #FFFF83"]Container Type
[/TD]
[TD="class: xl65, width: 146, bgcolor: #FFFF83"]Booked Delivery Date
[/TD]
[TD="class: xl65, width: 121, bgcolor: #FFFF83"]Container Arrived
[/TD]
[TD="class: xl65, width: 118, bgcolor: #FFFF83"]Container Tipped
[/TD]
[TD="class: xl66, width: 133, bgcolor: #FFFF83"]Container Departed
[/TD]
[TD="class: xl67, width: 201, bgcolor: transparent, colspan: 3"]DIF BOOKED TO TIP
[/TD]
[TD="class: xl70, width: 192, bgcolor: transparent, colspan: 3"]DIF ARRIVAL TO TIP
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]TCNU5303474
[/TD]
[TD="class: xl73, bgcolor: transparent"]40ft HC
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 04:00
[/TD]
[TD="class: xl73, bgcolor: transparent"]21/06/2014 06:21
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 11:18
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 20:06
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]06:21
[/TD]
[TD="bgcolor: transparent, align: right"]0.264583
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]04:57
[/TD]
[TD="bgcolor: transparent, align: right"]2.20625
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]HJCU1077530
[/TD]
[TD="class: xl73, bgcolor: transparent"]40ft HC
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 04:00
[/TD]
[TD="class: xl73, bgcolor: transparent"]21/06/2014 00:49
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 13:12
[/TD]
[TD="class: xl73, bgcolor: transparent"]24/06/2014 00:52
[/TD]
[TD="class: xl76, bgcolor: transparent, align: right"]07:18
[/TD]
[TD="bgcolor: transparent, align: right"]0.304167
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl76, bgcolor: transparent, align: right"]12:23
[/TD]
[TD="bgcolor: transparent, align: right"]0.515972
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]HJCU1523333
[/TD]
[TD="class: xl73, bgcolor: transparent"]40ft HC
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 04:00
[/TD]
[TD="class: xl73, bgcolor: transparent"]22/06/2014 19:41
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 05:37
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 11:38
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]14:48
[/TD]
[TD="bgcolor: transparent, align: right"]1.616667
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]22:24
[/TD]
[TD="bgcolor: transparent, align: right"]9.933333
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]GVDU5031900
[/TD]
[TD="class: xl73, bgcolor: transparent"]40ft HC
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 04:00
[/TD]
[TD="class: xl73, bgcolor: transparent"]22/06/2014 19:45
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 10:52
[/TD]
[TD="class: xl73, bgcolor: transparent"]23/06/2014 14:03
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]06:52
[/TD]
[TD="bgcolor: transparent, align: right"]0.286111
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]15:07
[/TD]
[TD="bgcolor: transparent, align: right"]0.629861
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Dates and times are just numbers to excel. The date is the whole number part and the time is the fraction part. Therefore 9.933 timewise is just 0.933 of a day. Multiply 0.933 * 24 and you see where the 22.24 is coming from. They are both the same number.
 
Upvote 0
If you want the formula to say the time difference in words:

=INT((B1-A1)*24)&" hours "&ROUND(((B1-A1)*24-INT((B1-A1)*24))*60,0)&" minutes"
 
Upvote 0
Thanks Steve - got it working whan I re read an earlier post and corrected the error!

Now all I have to do is work out how to display the negative times (where the container was tipped before its booked time....
 
Upvote 0

Forum statistics

Threads
1,223,195
Messages
6,170,661
Members
452,345
Latest member
ye4hb0i

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top