I have a sheet with various ID numbers in Column A with dates listed for that ID in Column B. See "Sample" rows 2-9.

I'm trying to populate Column C with an "end" date for any 1-day period or period of consecutive days in Column B, so that I can calculate the number of days between B and C.

So... if Column B shows 3/24/97 and the next row for Column B is a non-consecutive date, this is a 1-day period and the entry in C should be 3/25/97.

If Column B shows 3/28/97 and the next row is 3/29/97, this is a 2-day period and the entry for Column C on the row with 3/28/97 should be 3/30/97.

In that situation, I would love it if the row containing 3/29/97 was then removed. If that is not possible, then Column C for that row can be blank and I'll remove those rows manually.

For the Sample, Rows 13-17 show how the data needs to end up with the rows removed. Rows 21-28 shows what it would look like if there are blanks in Column C. Again, I can go back and manually remove them if need be.

Thanks in advance for your help!Sample.xlsx