grouping rows by time intervals of 30 mins

Hi everyone,

I have a huge dataset of fishing vessel tracking points. In each point (lat, long), a date and time is assigned. In order to organise my data for ArcGIS, I need to create "fishing events", i.e. groups of consecutive points that indicate a special fishing activity. To do so, I have to group my rows (each representing one point on the map I wanna create) based on time difference between consecutive points. More specifically: if the time between two consecutive points is more than 30 minutes, then these two points represent 2 different events.
An example below:

4/27/2019 04:11:34
4/27/2019 04:16:23
4/27/2019 04:30:57
4/27/2019 05:05:16
4/27/2019 08:10:32
4/27/2019 10:12:48

In this case, according to my rule, there are 4 different groups: 1 with 3 points and then the rest are represented by one point each.

Thank you very much in advance.

Originally Posted by Richard Buttrey

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Hi Richard and thaks for the answer!
I have uploaded a good representative file. I hope it's all clear!

Originally Posted by popipipo

Start in Cel A2 with the first date and time.
Put this formula in B2 and pul down.

Please Login or Register  to view this content.

Thanks a lot for the answer! Yes this would work fine I think, but for the "true" and "false" values, they should be different than the dates (A column). Let's say, they could be 1,2,3.. etc

Not even now?

it's just 35 KB

I'm really sorry for the inconvenience!
It's an excel file and I tried to upload both .xls and .xlsx but none of them worked. I think now it's finally attached!

Originally Posted by popipipo

Convert column to times it is now text.
Keep cells B1:C1 empty
and then you can use the formula in column E

Please Login or Register  to view this content.

awesome! thank you so much!