Date & Time
Calculate stopped dwell time from a fleet GPS timestamp & milepost log
Estimate time in unchanged-location intervals from an ordered timestamp and milepost log, with explicit sampling assumptions.
Estimate unchanged-location intervals from sampled pings
Equal milepost readings at two sampled endpoints do not prove that a vehicle was stationary throughout the interval: it could move and return, or the sensor may round positions. Conversely, GPS noise may change readings while a vehicle is stopped. This is a deliberately simple equality-based estimate, not exact dwell time or proof that an engine was idling.
Use numeric full date-time serials on one consistent time basis and in strictly increasing order. Dates are necessary for trips crossing midnight; time-only values cannot identify the next day. Missing locations, duplicate timestamps and reversed intervals are rejected.
Keep fractional minutes for each interval and round only for display or the final report. The estimate assigns the whole interval to unchanged location whenever its endpoint mileposts are equal. It does not detect partial stops between unequal endpoints or apply a GPS distance tolerance.
| GPS Ping Time (A) | Milepost (B) | Leg Activity | Estimated unchanged-location minutes (C) |
|---|---|---|---|
| 08:00 | 12.0 | Depart origin depot | — |
| 08:15 | 28.5 | Highway transit (+16.5 mi) | 0 min |
| 08:40 | 28.5 (Unchanged) | Stopped at Milepost 28.5 | 25 min |
| 09:05 | 28.5 (Unchanged) | Still stopped at Milepost 28.5 | 25 min (50 min total!) |
| 09:20 | 44.0 | Resumed transit (+15.5 mi) | 0 min (80 min total trip) |
Per-interval stopped minutes when location B3 equals prior location B2
Multiply the full date-time difference by 1440. Both mileposts must be numeric and both timestamps valid and strictly increasing.
=IF(COUNT(A2:B3)<>4,"Invalid inputs",IF(OR(A2<0,A3<=A2),"Invalid inputs",IF(B3=B2,(A3-A2)*1440,0)))Total estimated unchanged-location minutes
Sum all four validated interval estimates. The unchanged-location share is this total divided by elapsed time; its complement is not a measured moving-time utilization ratio. The final total is rounded to six decimal places in minutes to remove binary floating-point residue, not to imply that the sampling is that accurate.
=IF(COUNT(C3:C6)<>4,"Invalid inputs",ROUND(SUM(C3:C6),6))Try the example
Use the original values or change them to test your understanding.