Help desperately needed please?!

[jacjorjac]

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

 

[Michael M]

Hi
Isn't it simply

=IF(A3="","",A3/E3)

to give Km / Hr

 

[AlphaFrog]

I'm not sure I follow but here's my guess....

Excel Workbook
	A	B	C	D	E	F
1	Km	Arrive	Depart	Elapsed	Converted	Distance
2		Time	Time	Time	Decimal	Time
3	22	6:30 AM	6:55 AM	0:25	0.4167	?
4	0	7:15 AM	7:45 AM	0:30	0.5	N\A
5	12	8:15 AM	10:05 AM	1:50	1.8333	0:30
...

Cell Formulas
Range	Formula
F4	=IF(AND(A4>0,B4>C3), B4-C3, "N\A")
F5	=IF(AND(A5>0,B5>C4), B5-C4, "N\A")

Format column F as Time 13:30

 

[jacjorjac]

wow! Thank you so much - that worked a treat, and solved a huge dilemma for me - can I be cheeky and ask if you can solve another issue with my sheet?

I have drivers that do an action (lift off or on containers, etc) that is done all at the same time, how can I only get it to state the elapsed time just once?
I need;
If the same time is entered for example, 11:40AM - 12:00PM elapsed time is 20 mins. The next entry will have 11:40AM - 12:00PM but the entry in elapsed time should read 0:00 not 20 mins also.

Duplicate start/finish times are giving duplicate elapsed times which isn't true - it only took 20 mins not 40 mins.

<TABLE style="WIDTH: 643pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=856 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6656" width=182><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18>M</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>N</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=70>O</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 137pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=182>P</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=71>Q</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85>R</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>S</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>T</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>U</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>V</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>W</TD></TR><TR style="HEIGHT: 51.75pt" height=69><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: transparent" width=64 height=69>Location</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Suburb</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff" width=70>20' OR 40' CONTAINER</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 137pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=182>ACTIONS</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=71>Start/Arrive </TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=85>Finish/Depart</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Elapsed Time in Minutes</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Approx Travelling Time</TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Converted </TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #b6dde8" width=64>Labour Rate</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccc0da" width=64>Hourly Rate + 42% On Costs</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MRS</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SOMERTON</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">20'</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LIFT OFF EMPTY CONTAINER</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11:40 AM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">12:00 PM</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:20</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">N/A</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.2500</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b6dde8">$19.42</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccc0da">$6.89</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MRS</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SOMERTON</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">20'</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LIFT ON EMPTY CONTAINER</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11:40 AM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">12:00 PM</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0:20</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">N/A</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.2500</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b6dde8">$19.42</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccc0da">$6.89</TD></TR></TBODY></TABLE>


Your help with this is going to be huge!

Thank you

jacjorjac

 

[Michael M]

How can you determine it is the same driver, doing the same task ??
IS there a reference No or something.
So, if Ref No in row 2 = Ref No in row 1, ET will be zero ??

I'll take a guess at:....and copy down as required.

<body><b>Sheet1</b><br><br><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:90px;"><col style="width:64px;"><col style="width:102px;"><col style="width:108px;"><col style="width:64px;"><col style="width:88px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td><td>U</td><td>V</td><td>W</td></tr><tr style="height:64px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-size:10pt; ">Location</td><td style="font-size:10pt; ">Suburb</td><td style="background-color:#cc99ff; font-size:10pt; ">20' OR 40' CONTAINER</td><td style="font-size:10pt; ">ACTIONS</td><td style="font-size:10pt; ">Start/Arrive</td><td style="font-size:10pt; ">Finish/Depart</td><td style="font-size:10pt; ">Elapsed Time in Minutes</td><td style="font-size:10pt; ">Approx Travelling Time</td><td style="font-size:10pt; ">Converted</td><td style="background-color:#ccccff; font-size:10pt; ">Labour Rate</td><td style="background-color:#c0c0c0; font-size:10pt; ">Hourly Rate + 42% On Costs</td></tr><tr style="height:63px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-size:10pt; ">MRS</td><td style="font-size:10pt; ">SOMERTON</td><td style="font-size:10pt; ">20'</td><td style="font-size:10pt; ">LIFT OFF EMPTY CONTAINER</td><td style="font-size:10pt; text-align:right; ">11:40 AM</td><td style="font-size:10pt; text-align:right; ">12:00 PM</td><td style="font-size:10pt; text-align:right; ">0:20</td><td style="font-size:10pt; ">N/A</td><td style="font-size:10pt; text-align:right; ">0.25</td><td style="background-color:#ccccff; font-size:10pt; text-align:right; ">$19.42</td><td style="background-color:#c0c0c0; font-size:10pt; text-align:right; ">$6.89</td></tr><tr style="height:32px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-size:10pt; ">MRS</td><td style="font-size:10pt; ">SOMERTON</td><td style="font-size:10pt; ">20'</td><td style="font-size:10pt; ">LIFT ON EMPTY CONTAINER</td><td style="font-size:10pt; text-align:right; ">11:40 AM</td><td style="font-size:10pt; text-align:right; ">12:00 PM</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">N/A</td><td style="font-size:10pt; text-align:right; ">0.25</td><td style="background-color:#ccccff; font-size:10pt; text-align:right; ">$19.42</td><td style="background-color:#c0c0c0; font-size:10pt; text-align:right; ">$6.89</td></tr></tbody></table><br><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td><b>Spreadsheet Formulas</b></td></tr><tr><td><table border="1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>S3</td><td>=IF(AND<span style=" color:008000; ">(Q3=Q2,R3=R2)</span>,"",R3-Q3)</td></tr></tbody></table></td></tr></tbody></table> <br><br><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

</body>

 

[jacjorjac]

Hi there, exactly! I have a Job No in row A that is for a particular container being moved by many drivers at any point in time during the week.
How would I get it to reference the unique Job No in row A - no matter when it turns up and who drives it?

thank you Thank you

 

[AlphaFrog]

In your post #4, who are you taking to? Which solution worked a treat?

Excel Workbook
	M	N	O	P	Q	R	S	T	U	V	W
1	Location	Suburb	20' OR 40' CONTAINER	ACTIONS	Start/Arrive	Finish/Depart	Elapsed Time in Minutes	Approx Travelling Time	Converted	Labour Rate	Hourly Rate + 42% On Costs
2	MRS	SOMERTON	20'	LIFT OFF EMPTY CONTAINER	11:40 AM	12:00 PM	0:20	N/A	0.25	$19.42	$6.89
3	MRS	SOMERTON	20'	LIFT ON EMPTY CONTAINER	11:40 AM	12:00 PM	0:00	N/A	0.25	$19.42	$6.89
...

Cell Formulas
Range	Formula
S2	=IF(M1&N1&O1&Q1&R1<>M2&N2&O2&Q2&R2, R2-Q2, 0)
S3	=IF(M2&N2&O2&Q2&R2<>M3&N3&O3&Q3&R3, R3-Q3, 0)

 

[Michael M]

So, maybe
and copy down
OR
ADD a reference to column A in AlphaFrogs formula

<body><b>Sheet1</b><br><br><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:90px;"><col style="width:64px;"><col style="width:102px;"><col style="width:108px;"><col style="width:64px;"><col style="width:88px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td><td>U</td><td>V</td><td>W</td></tr><tr style="height:64px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-size:10pt; ">Location</td><td style="font-size:10pt; ">Suburb</td><td style="background-color:#cc99ff; font-size:10pt; ">20' OR 40' CONTAINER</td><td style="font-size:10pt; ">ACTIONS</td><td style="font-size:10pt; ">Start/Arrive</td><td style="font-size:10pt; ">Finish/Depart</td><td style="font-size:10pt; ">Elapsed Time in Minutes</td><td style="font-size:10pt; ">Approx Travelling Time</td><td style="font-size:10pt; ">Converted</td><td style="background-color:#ccccff; font-size:10pt; ">Labour Rate</td><td style="background-color:#c0c0c0; font-size:10pt; ">Hourly Rate + 42% On Costs</td></tr><tr style="height:63px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-size:10pt; ">MRS</td><td style="font-size:10pt; ">SOMERTON</td><td style="font-size:10pt; ">20'</td><td style="font-size:10pt; ">LIFT OFF EMPTY CONTAINER</td><td style="font-size:10pt; text-align:right; ">11:40 AM</td><td style="font-size:10pt; text-align:right; ">12:00 PM</td><td style="font-size:10pt; text-align:right; ">0:20</td><td style="font-size:10pt; ">N/A</td><td style="font-size:10pt; text-align:right; ">0.25</td><td style="background-color:#ccccff; font-size:10pt; text-align:right; ">$19.42</td><td style="background-color:#c0c0c0; font-size:10pt; text-align:right; ">$6.89</td></tr><tr style="height:32px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-size:10pt; ">MRS</td><td style="font-size:10pt; ">SOMERTON</td><td style="font-size:10pt; ">20'</td><td style="font-size:10pt; ">LIFT ON EMPTY CONTAINER</td><td style="font-size:10pt; text-align:right; ">11:40 AM</td><td style="font-size:10pt; text-align:right; ">12:00 PM</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">N/A</td><td style="font-size:10pt; text-align:right; ">0.25</td><td style="background-color:#ccccff; font-size:10pt; text-align:right; ">$19.42</td><td style="background-color:#c0c0c0; font-size:10pt; text-align:right; ">$6.89</td></tr></tbody></table><br><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td><b>Spreadsheet Formulas</b></td></tr><tr><td><table border="1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>S3</td><td>=IF(A3=$A$2,"",R3-Q3)</td></tr></tbody></table></td></tr></tbody></table> <br><br><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

</body>

 

[jacjorjac]

sorry Alpha Frog, I was so excited to get a reply, Thank you Michael M - I just tried yours Alpha Frog and I can see that it doesn't give a blank return like Michaels one. Thank you anyway!
Michael M - using your new solution
<TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR><TD>S3</TD><TD>=IF(A3=$A$2,"",R3-Q3)</TD></TR></TBODY></TABLE>
can I apply this if I want to sum up some columns of Costs with these unique Job No's in ColumnA?

These Job No's are unique however they can occur at any time with any driver.

Thank you so much!

jacjorjac

 

[Michael M]

AlphaFrogs will give a blank as well....
just change the 0 to ""

=IF(M1&N1&O1&Q1&R1<>M2&N2&O2&Q2&R2, R2-Q2, "")