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>>

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

<tbody>
</tbody>