Bit difficult to describe but to do with date/times from a log.

[Reshaw]

Apologies for the vague title but I'm not sure how best to describe this challenge.

I have a log from our GPS tracking system for all of our company trailers. I am trying to work out how long each trailer stayed on site at a particular destination for.

The first complication is that for each trailer there are several pings (lines in the log) that will have a start date/time and an end date/time. The trailer may or may not have moved in between the pings.

So I have written a function that can detect the first and last rows that pertain to a single trailer that has not moved.
This looks like:-
=IF(OR(C2<>C1, A2<>A1, AND(A2=A1, C2=C1, G1>0.833)), "BEGIN", "") //Cell F2 below
and
=IF(OR(C4<>C3, A4<>A3, AND(A4=A3, C4=C3, G3>0.833)), "END", "") //Cell G2 below

What I need to do now is to calculate the time between the first and arrival date/time and the last departure date/time with a variable number of rows in between. I tried using nested if statements to keep looking one higher but it maxes out at 7 iterations and some are longer than that. I am looking for an automatic way of doing this so that I can speed up a process of creating this invoice each week.

On the table below, I need to calculate the difference between B2 and c3, then b4 and c7, b8 and c9, b10 and c10. Please note there may be only one row.

I anticipate a function that checks to see if column G# = "END" then looks for the previous Begin and looks up the arrival and departure dates/times. But I am struggling to find a way to do this without the if statement.

Any suggestions are appreciated.
Thanks
Rob


Sample Data below>>

[TABLE="width: 609"]
<tbody>[TR]
[TD]Vehicle[/TD]
[TD]Arrival[/TD]
[TD]Departure[/TD]
[TD]Duration[/TD]
[TD]time between Shifts[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]01/09/2014 04:43[/TD]
[TD="align: right"]01/09/2014 16:12[/TD]
[TD="align: right"]11:29:04[/TD]
[TD]00:03:51[/TD]
[TD]BEGIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]01/09/2014 16:16[/TD]
[TD="align: right"]02/09/2014 11:24[/TD]
[TD="align: right"]19:07:33[/TD]
[TD]312:02:04[/TD]
[TD][/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]15/09/2014 11:26[/TD]
[TD="align: right"]15/09/2014 11:27[/TD]
[TD="align: right"]00:01:03[/TD]
[TD]00:34:00[/TD]
[TD]BEGIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]15/09/2014 12:01[/TD]
[TD="align: right"]17/09/2014 10:43[/TD]
[TD]1.22:42:25[/TD]
[TD]00:00:02[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]17/09/2014 10:43[/TD]
[TD="align: right"]17/09/2014 16:03[/TD]
[TD="align: right"]05:20:09[/TD]
[TD]00:03:51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL001[/TD]
[TD="align: right"]17/09/2014 16:07[/TD]
[TD="align: right"]17/09/2014 16:57[/TD]
[TD="align: right"]00:49:37[/TD]
[TD][/TD]
[TD][/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]10/09/2014 06:27[/TD]
[TD="align: right"]11/09/2014 02:46[/TD]
[TD="align: right"]20:19:01[/TD]
[TD]00:03:41[/TD]
[TD]BEGIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]11/09/2014 02:49[/TD]
[TD="align: right"]11/09/2014 13:18[/TD]
[TD="align: right"]10:28:19[/TD]
[TD]283:52:53[/TD]
[TD][/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]23/09/2014 09:11[/TD]
[TD="align: right"]24/09/2014 07:26[/TD]
[TD="align: right"]22:15:06[/TD]
[TD]297:20:53[/TD]
[TD]BEGIN[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]06/10/2014 16:47[/TD]
[TD="align: right"]07/10/2014 18:23[/TD]
[TD="align: right"]25:35:58[/TD]
[TD]00:00:02[/TD]
[TD]BEGIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]07/10/2014 18:23[/TD]
[TD="align: right"]08/10/2014 06:58[/TD]
[TD="align: right"]12:35:02[/TD]
[TD]00:04:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]08/10/2014 07:02[/TD]
[TD="align: right"]08/10/2014 09:38[/TD]
[TD="align: right"]02:36:29[/TD]
[TD]00:11:07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]08/10/2014 09:49[/TD]
[TD="align: right"]08/10/2014 09:52[/TD]
[TD="align: right"]00:03:10[/TD]
[TD]00:12:51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APL002[/TD]
[TD="align: right"]08/10/2014 10:05[/TD]
[TD="align: right"]08/10/2014 10:54[/TD]
[TD="align: right"]00:48:27[/TD]
[TD][/TD]
[TD][/TD]
[TD]END[/TD]
[/TR]
</tbody>[/TABLE]