Exceljet

Quick, clean, and to the point

Formula puzzle - how long was the truck stopped?

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:

Spreadsheet of truck location data at each hour of the day
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.

The puzzle

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.

Assumptions

  1. There are 5 locations with these names: A, B, C, D, E
  2. 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!