Tracking Data Across Multiple Rows

[Cmcguire]

I have a file that lists events, the date their statuses changed, the old status and the changed status. I've attached dummy data to represent 2 simple scenarios of this. For anything that doesn't repeat data, I've been able to find the time between status by joining the new status with the old status on the same data. If an event ever returns to another status however, excess data is created. Some of this can easily be cleaned up by removing anything with a negative in the difference between the dates. However in other situations, its less obvious. There's probably a better way to go about doing this, so I'm not bound to using this join method to find the data. Is there an easier way that I'm missing or am I missing a simple solution for cleaning up the join? Thanks for the help!

[Luke_C · Accepted solution]

Hi @Cmcguire 

I'm not exactly sure on the final output you need, but maybe you could use a multi-row formula to look for the number of days between each record?

[Cmcguire]

This was my first time playing around with that tool, so I had no idea it existed! With a bit of editing, I can get this to represent exactly what I want to show! Appreciate the quick and exampled response a ton!