help identifying deliveries greater than 2 hours apart as a separate delivery

[Chris_D]

I am trying to categorise some deliveries by creating a "drop code" as an identifier. the drop code needs to be the delivery postcode + date item ordered + the time it was delivered.

the catch is that any item to the same postcode on the same day that are larger than 2 hours apart on the last scan need to be shown as a separate delivery. I need help creating the "drop that day" column to be able to finish making my drop code column.

much appreciated for any help

postcode	last scan	date sent	Drop that day	drop code ('=A1& " " &C1& " " &D1)
B20 3JQ	10/02/2017 12:30	09/02/2017	drop 1	B20 3JQ 42775 drop 1
B20 3JQ	10/02/2017 12:55	09/02/2017	drop 1	B20 3JQ 42775 drop 1
B20 3JQ	10/02/2017 18:00	09/02/2017	drop 2	B20 3JQ 42775 drop 2
B20 3JQ	10/02/2017 18:30	09/02/2017	drop 2	B20 3JQ 42775 drop 2
B20 3JQ	11/02/2017 12:30	10/02/2017	drop 1	B20 3JQ 42776 drop 1
b20 4JL	11/02/2017 12:55	10/02/2017	drop 1	b20 4JL 42776 drop 1
B60 2BS	11/02/2017 18:00	10/02/2017	drop 1	B60 2BS 42776 drop 1
B30 5DD	11/02/2017 18:30	10/02/2017	drop 1	B30 5DD 42776 drop 1
B30 5DD	11/02/2017 21:30	10/02/2017	drop 2	B30 5DD 42776 drop 2

<tbody>
</tbody>

 

[oldbrewer]

postcode	last scan	date sent	Drop that day	drop code ('=A1& " " &C1& " " &D1)
B20 3JQ	10/02/2017 12:30	09/02/2017	drop 1	B20 3JQ 42775 drop 1		drop1
B20 3JQ	10/02/2017 12:55	09/02/2017	drop 1	B20 3JQ 42775 drop 1		drop1	#####
B20 3JQ	10/02/2017 18:00	09/02/2017	drop 2	B20 3JQ 42775 drop 2		drop2
B20 3JQ	10/02/2017 18:30	09/02/2017	drop 2	B20 3JQ 42775 drop 2		drop2
B20 3JQ	11/02/2017 12:30	10/02/2017	drop 1	B20 3JQ 42776 drop 1		drop1
b20 4JL	11/02/2017 12:55	10/02/2017	drop 1	b20 4JL 42776 drop 1		drop1
B60 2BS	11/02/2017 18:00	10/02/2017	drop 1	B60 2BS 42776 drop 1		drop1
B30 5DD	11/02/2017 18:30	10/02/2017	drop 1	B30 5DD 42776 drop 1		drop1
B30 5DD	11/02/2017 21:30	10/02/2017	drop 2	B30 5DD 42776 drop 2		drop2
			###### (G3)
			=IF(C3<>C2,"drop1",IF(AND(C3=C2,B3-B2>(2/24),A3=A2),"drop2",G2))
			G2 is by definition drop1

<colgroup><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Chris_D]

Many thanks oldbrewer,

I have had a play about for a couple of hours and I cant quite get it to work, here's what I get when I use the formula, it seems to have trouble when calculating the difference between the time on the same date, for example the result in F3

postcode	last scan	date sent	Drop that day	drop code ('=A1& " " &C1& " " &D1)	oldbrewer		oldbrewer_formulatext in column F
B20 3JQ	10/02/2017 12:30	09/02/2017	drop 1	B20 3JQ 42775 drop 1	0		=IF(C3<>C2,"drop1",IF(AND(C3=C2,B3-B2>(2/24),A3=A2),"drop2",$G$2))
B20 3JQ	10/02/2017 12:55	09/02/2017	drop 1	B20 3JQ 42775 drop 1	drop2	#####	=IF(C4<>C3,"drop1",IF(AND(C4=C3,B4-B3>(2/24),A4=A3),"drop2",$G$2))
B20 3JQ	10/02/2017 18:00	09/02/2017	drop 2	B20 3JQ 42775 drop 2	0		=IF(C5<>C4,"drop1",IF(AND(C5=C4,B5-B4>(2/24),A5=A4),"drop2",$G$2))
B20 3JQ	10/02/2017 18:30	09/02/2017	drop 2	B20 3JQ 42775 drop 2	drop1		=IF(C6<>C5,"drop1",IF(AND(C6=C5,B6-B5>(2/24),A6=A5),"drop2",$G$2))
B20 3JQ	11/02/2017 12:30	10/02/2017	drop 1	B20 3JQ 42776 drop 1	0		=IF(C7<>C6,"drop1",IF(AND(C7=C6,B7-B6>(2/24),A7=A6),"drop2",$G$2))
b20 4JL	11/02/2017 12:55	10/02/2017	drop 1	b20 4JL 42776 drop 1	0		=IF(C8<>C7,"drop1",IF(AND(C8=C7,B8-B7>(2/24),A8=A7),"drop2",$G$2))
B60 2BS	11/02/2017 18:00	10/02/2017	drop 1	B60 2BS 42776 drop 1	0		=IF(C9<>C8,"drop1",IF(AND(C9=C8,B9-B8>(2/24),A9=A8),"drop2",$G$2))
B30 5DD	11/02/2017 18:30	10/02/2017	drop 1	B30 5DD 42776 drop 1	drop2		=IF(C10<>C9,"drop1",IF(AND(C10=C9,B10-B9>(2/24),A10=A9),"drop2",$G$2))
B30 5DD	11/02/2017 21:30	10/02/2017	drop 2	B30 5DD 42776 drop 2	drop1		=IF(C11<>C10,"drop1",IF(AND(C11=C10,B11-B10>(2/24),A11=A10),"drop2",$G$2))

<colgroup><col width="68" style="width:51pt"> <col width="128" style="width:96pt"> <col width="83" style="width:62pt"> <col width="100" style="width:75pt"> <col width="245" style="width:184pt"> <col width="71" style="width:53pt"> <col width="57" style="width:43pt"> <col width="514" style="width:386pt"> </colgroup><tbody>
</tbody>

thanks again for your help and time