Calculating duration of time between Entry and Exit across Rows with data anomalies

[gavin_chin]

i have car parking entry and exit data from our car park operator based on 'swipe cards'. I would like to calculate the duration of time between entry and exit for each particular set of instances (one entry and one exit per instance). However, the data layout and data itself is quite quirky and i am unsure how to resolve.

- Parking can sometimes occur across 2 days (entry 5pm Monday Exit 5am Tuesday)
- The entry and exit data is all presented in the same column
- sometimes there are anomalies with the data e.g. an Entry appears but no Exit swipe for 3/2/2021 - is there a way to assume an exit after 24 hours and create a dummy "exit entry'?

i set out an example below. 

[OllieClarke · Accepted solution]

@gavin_chin Here's my approach:

It assumes that there will be an Entry per visit, even if there isn't an Exit. Hopefully that's a fair assumption and this helps 🙂

[Emil_Kos]

Hi @OllieClarke,

A really elegant solution. I was working on my won workflow but your is so nice that I don't think I need to create my won. 

[Qiu]

@gavin_chin 
If your date is in good order.

[gavin_chin]

@OllieClarke Thanks for the quick response Ollie!  much appreciated.

what happens if sometimes there is also an "exit" but not an "entry"

im not sure if its a glitch, or if its for some other unplanned reason e.g. someone physically walking out of the carpark to have a break or something

[OllieClarke]

@gavin_chin if there can be exits, but no entries, then it gets trickier. Basically we need *something* to signify a new session. I guess you could construct some logic that creates a new ID every time there's an entry, or if you have consecutive rows of the same (e.g. Exit,Exit or Entry,Entry). Alternatively you could try and do something with the datetimes and assumptions of how long sessions are, but I think that would be less accurate