Difference between dates in columns with multiple values

[LuciferMstar]

Hello there, helpful people,

I might need your brain power regarding a few calculations that at first I thought it made sense theoretically, but in practice I can't wrap my head around it. 

Basically I have this kind of table, think of it as a pivot based on different statuses with dates for the same ID, and I want to calculate the difference between statuses, based on a simple logic and a few conditions as you can see down below. 

Result 1 = if [Status 2] > [Status 1] and [Status 2] < [Status 7] then Diff [Status 7] - [Status 2]
Result 2 = if [Status 2] > [Status 7] and [Status 2] < [Status 3] then Diff [Status 3] - [Status 2]
Result 3 = if [Status 2] > [Status 3] and [Status 2] < [Status 4] then Diff [Status 4] - [Status 2]
Result 4 = if [Status 2] > [Status 4] and [Status 2] < [Status 5] then Diff [Status 5] - [Status 2]
Result 5 = if [Status 2] > [Status 5] and [Status 2] < [Status 6] then Diff [Status 6] - [Status 2]

ID	Status 1	Status 2	Status 3	Status 4	Status 5	Status 6	Status 7
153				06/04/2022
153	18/01/2022
153	15/02/2022
153			06/04/2022
153							06/04/2022
153					06/04/2022
153						12/04/2022
153		02/02/2022
153		23/03/2022

Many thanks!

[Anonymous]

Hi @LuciferMstar ,

I am confused about your calculation logic. I can see two dates in Status 2 for ID 153. Should both dates in Status 2 larger than both dates in Status 1 for each ID or just need one meet your logic? 

Please show me the result you want and share a sample file with me. This will make me easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LuciferMstar]

Hi @Anonymous ,

Let's take a simple approach, on the same table.

Because the data looks like that I still didn't figure it out how to compare if each value on Status 2 is greater than each value on Status 1, and if it is, then perform a DATEDIFF where the condition is true. Whatever I tried it looks on the same row, but because I have blank it returns blank.

[LuciferMstar]

Hi @Anonymous , 

Thank you for taking the time to look at my post.

The logic would be: for each ID, it should take each date from [Status 2] and compare it against the other dates on other [Status] columns based on the conditions for each Result as you can see above the table in the OG post.

The result would be something like this, if it helps:

Thank you,