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

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.

example entry exit data.xlsx

Tips and Tricks

Parse

Preparation

Expression

Accepted answers

OllieClarke

@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 🙂

gavin_chin.yxmd

All comments

OllieClarke

@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 🙂

gavin_chin.yxmd

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.

New Workflow3.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335