Using multirow formula between two columns

PNC97

I'm trying for a solution where multiple columns has datetime as below table.

I've tried using multirow formula "IF ID = Row-1:ID And End = Row-1:Start  and applied rank formula but I'm not getting expected output.

And I'm trying to find a solution where to get an expected output in excel sheet.

Thanks for the help in advance.

data.xlsx

Qiu

@PNC97 
 I have basically followed the idea of @apathetichell and prepared a sample flow.

0114-PNC97.yxzp