jacjorjac
Board Regular
- Joined
- Sep 9, 2010
- Messages
- 51
Hi Guru's out there!
I have a curly one that I have tried for so long to work out on my own ...
I have runsheets from truck drivers with arrive, depart, elapsed time and kilometers travelled.
If I wanted to see how much time was spent driving from point C2 to arriving at Point B3 – how would I do this please?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Eg – Time spent driving only when there is a kilometre entry in column A<o></o>
<o> </o>
DEPART at 07:45AM<o></o>
ARRIVE at 08:15AM when kilometres were 12?<o></o>
<o> </o>
If no kilometres – then a false or N/A entry in column F
example
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18>A</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>B</TD><TD class=xl86 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>C</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>D</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>E</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbeef3" height=17>Km</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Arrive</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Depart</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Elapsed</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Converted</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Distance</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #dbeef3" height=18> </TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl88 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Decimal</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>22</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06:30AM</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06:55AM</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:25</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.4167</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07:15AM</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07:45AM</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:30</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.5</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">08:15AM</TD><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10:05 AM</TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1:50</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.8333</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
My formulas at the moment are;
=IF(B2>C2,C2+1-B2,C2-B2) for D (Time Elapsed)<o></o>
= C2*24-B2*24 for E (Converted Decimal)<o></o>
Thank you all for so much, you have helped me go from terrified to quietly confident with Excel.
<o> </o>
Cheers!
<o></o>jacjorjac
I have a curly one that I have tried for so long to work out on my own ...
I have runsheets from truck drivers with arrive, depart, elapsed time and kilometers travelled.
If I wanted to see how much time was spent driving from point C2 to arriving at Point B3 – how would I do this please?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Eg – Time spent driving only when there is a kilometre entry in column A<o></o>
<o> </o>
DEPART at 07:45AM<o></o>
ARRIVE at 08:15AM when kilometres were 12?<o></o>
<o> </o>
If no kilometres – then a false or N/A entry in column F
example
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18>A</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>B</TD><TD class=xl86 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>C</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>D</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>E</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbeef3" height=17>Km</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Arrive</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Depart</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Elapsed</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Converted</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #dbeef3">Distance</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #dbeef3" height=18> </TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl88 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Decimal</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #dbeef3">Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>22</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06:30AM</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06:55AM</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:25</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.4167</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07:15AM</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07:45AM</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:30</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.5</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">08:15AM</TD><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10:05 AM</TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1:50</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.8333</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
My formulas at the moment are;
=IF(B2>C2,C2+1-B2,C2-B2) for D (Time Elapsed)<o></o>
= C2*24-B2*24 for E (Converted Decimal)<o></o>
Thank you all for so much, you have helped me go from terrified to quietly confident with Excel.
<o> </o>
Cheers!
<o></o>jacjorjac