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]