Aggregating Daily Ship Logs Between Departures and Arrivals (with Overlapping Timestamps) – I can’t figure out how to go about this, and I’ve tried 3 different variations but none of them seem to work.

![Photo of spreadsheet|690x352](upload://6DrmmtUY9SSaOyZ17jiiQ1dH2O5.jpeg)

- Trip Definition: From one Departure event to the next Arrival event. While underway (which can take several days), there’s a daily report (type = “Position”) recording distance traveled that day (in the “distanceLogged” column) and the speed ("loggedSpeed").

- Report Start/End UTC: Each daily row has:

  - Report Start UTC: The time the previous day’s report was submitted.
  - Report End UTC: The time the current day’s report is submitted.
  - Because each new report’s start matches yesterday’s end, these timestamps get reused (doubled up).

- What We Want:

  1. For each "Departure", locate the corresponding "Arrival" (with a different port name).
  2. Gather all daily reports (type = "Position") whose start/end times fall between the Departure and Arrival.
  3. Sum up the daily distances and average speeds for the entire trip.
  4. (Future) Use these same trip spans to calculate total fuel consumption, once engine data is available.

- Key Point: “Report End UTC” from one day is used as the next day’s “Report Start UTC,” so the dataset is chained day-to-day until the arrival event is reached.

[TimeAtSea.knwf|attachment](upload://qgs8OZo08nhvCbM2PcVsXkqjVnc.knwf) (101.9 KB)

Thank you

Hi @Adrien_2, Can you please place your Workflow link outside Preformatted text?

TimeAtSea.knwf (101.9 KB)

You’ve got the data stored locally. Its not available in the workflow. Create a “data” folder in your workflow and store the data there.