I have one layer of point data representing GPS tracks collected during vessel surveys (an individual survey is indicated as the time between departing and returning to shore and there can be more than one on a given date i.e. when we returned to shore and then went out again). I also have layers of 1 and 2 kmsq polygon grids. I would like to calculate the time spent 'on survey effort' in each cell to adjust for effort in other analyses. I intend to adjust the weighting of each cell for these other calculations depending on the amount of effort spent in the cell, i.e. adjusted density of whale sightings based on the search effort in that cell.
So far I have used a Spatial Join in ArcGIS to give the effort points that fall into a particular grid cell the unique identifier of the grid cell. I then exported the table to Excel to calculate the time spent on effort in each cell using the unique identifier of that cell. This was calculated by summing the times (on a given date and survey) between the points collected along that track in that cell and then using a Pivot Table to sum the total survey effort in each cell by the cell's ID and the survey number. Unfortunately, I realized after doing this that the total time on effort is different at the two different spatial scales of 1 and 2 kmsq. I believe this is because the calculation is missing the times the survey track crossed into a new cell i.e. the points fall in different cells so the time between these points was excluded.
I would really appreciate if anybody has a good idea of how I could calculate the sum of these survey effort times without losing the times during crossover from one cell to another. I think I need to make the point data into line data and extract the times when the line crosses into a new grid cell but I'm not sure the best way to do this. Any thoughts?