A couple weeks ago, a reader sent me an interesting question about tracking the "stopped time" for a fleet of trucks. The trucks are tracked by GPS so a location is recorded at each hour of the day for each truck. The data looks something like this:
The challenge: what formula in column N will correctly calculate total hours stopped?
I've simplified this a bit by replacing actual GPS coordinates with locations labeled A-E, but the concept remains the same.
For how many hours was each truck stopped?
Or, in Excel-speak:
What formula will calculate the total hours each truck was stopped?
For example, we know Truck1 was stopped for 1 hour because it's location was recorded as "A" at both 4 PM and 5 PM.
There are 5 locations with these names: A, B, C, D, E
A truck at the same location for two consecutive hours = 1 hour stopped
Got a formula that will do it?
Download the workbook and share your formula in the comments below. As with so many things in Excel, there are many ways to solve this problem!