Sequencing a series of dates and calculating the time variance

[Martin_Miller]

Hello Community!

I'm looking for some help to take a series of status change dates made against a record and record the time that record was held in the respective status'.

I have attached a screen shot of a single Work Order record and have summarized the status change dates and sorted the dates in ascending order.

Is there a way to take the next status date in the series and "create a new row" that captures the next date in the time series to generate a date / time calculation?

For example, I want to see the time it took to go from WAPPR (2016-10-04) to WENG (2016-10-04) - ~24minutes.  This would require me to pull in the change date of WENG to a NEW column next to the previous status of WAPPR - or so this is my mental model (image attached for reference).

Thanks in advance for the help! 

[Kenda · Accepted solution]

Hey @Martin_Miller! If you add a Multi-Row Formula tool and create a new field with the following Expression:

DateTimeDiff([changedate],[Row-1:changedate],"minutes")

Does that accomplish what you're wanting?

[GavinAttard]

Hi @Martin_Miller

The Cross tab tool should do the trick. 

Group on Work Order. Name from Status and value from change date. 

Example attached. 

[Martin_Miller]

That was the trick...Thank you!