Calculating time length based on cell references

Hi everyone,

I am trying to calculate total time by subtracting a later time from an earlier time based upon when the "POD POS" changes back to a value of 1 in the example below:

In the following set of data where time is in one column and "POD POS" is in another:

TIME POD POS
5:08:09 1
5:08:27 2
5:08:44 3
5:09:14 4
5:09:28 5
5:09:44 6
5:10:14 7
5:12:02 1
5:12:12 2
5:12:42 3
5:13:12 4
5:13:42 5
5:14:12 6
5:14:42 7
5:20:57 1
5:21:27 2
5:21:58 3
5:22:27 4
5:22:57 5
5:27:25 1

I need to know what the total time elapsed between 1 and 7 is, etc. until the number in the "POD POS" column changes back to 1, then I need to repeat. So in the first set that runs from 1 to 7, I need to return a value of what 5:10:14 - 5:08:09 is. The string of data does not always end at 7 items, and could be longer or shorter. This data goes on for about 4000 rows, so it is something I need to replicate down a column. I need to return a total in the first set of 1-7 of 0:02:05 (the seventh value minus the first), and repeat that in the next set for a value of 0:02:40, and so on.

Any thoughts or insight are greatly appreciated.

See the attached. Copy formula in C2 down as far as you need to.

Hope this helps.

Pete

Thanks, this nearly worked. It did work as advertised for the short list of data I sent though.

I ultimately found another solution that made my client happy.