How to find Datetime difference between dates in two rows

[Tresa Sky]

Hello,

Need help!

I need to find the time difference between the datetime that are in 2 separate rows. And how will I apply that logic to the rest of the data in file

Sample data:

Column1. Column2.

Row 1. 2019-08-01 12:30:15. Hold

Row 2. 2019-08-01 12:35.20. Release

Row 3. 2019-08-01 3:40:05. Hold

Row 4. 2019-02-01 3:42:09. Release

How would I find the difference between the Hold and the Release date/time

 

Thanks!

[Nghiem Dan]

1. Did you tried pivot

 

2. Another way is creat sub column

 

Column 3: rank([col1],[col2])

 

Creat copy table. Left join by column 3

[Antoine Doeraene]

I perhaps have a solution. It will only work if your data are exactly alternating between holds and releases (so no overlapping periods).

Create a column with the id of the row (so insert calculated column whose expression is `RowId()`). Let's call this column "RowId".

Then, you can create yet another column call "Differences" with the formula

DateDiff([Column1.],Last([Column1.]) over (Previous([RowId])))

Hope that helps.