Matching Gate Time Stamp Records

[Bobbins]

Good Morning All,

Taking gate data, I am trying to work out how long individuals spend on site (Preferably in a single go so happy to record multiple times on site for the same day/shift. Useful things to know:

• Over 10,000 gate movements a day (so person does not enter / leave site on the next row)
• May work a night shift or day shift and may flip between them
• Multiple different gates
• Person may exit site and not return for 20 days
• Person may enter and exit site multiple times in a day

My data format is shown as the following:

Record ID	Name	Gate	Work Date	Work Site	IN/OUT	URN
50	Matt Smith	Alpha	10/07/2022	21:35:01	IN	20548636
Some time much later........	......		.....	.....	....	.....
2500	Matt Smith	Beta	11/07/2022	05:35:26	OUT	20548636

How would you do this given information and useful things to know? I was hoping to achieve something similar to:

Name	Gate In	Gate Out	Date/Time Stamp In	Date/Time Stamp Out	HH:MM	URN
Matt Smith	Alpha	Beta	10/07/2022 21:35:01	11/07/2022 05:53:26	08:00	20548636

I was thinking that i could create time/date stamp, organize by this and then match via URN but its how to match to next timestamp for that URN that causing my brainache this morning

Thank you

[grazitti_sapna]

Hello @Bobbins, would it be possible for you to provide a sample dataset?

Thanks!

[grazitti_sapna]

Hi @Bobbins, since there is not much data so I tried to create something from your inputs.

Give this a try and let us know if it works for you?

Thanks!

[OllieClarke]

HI @Bobbins how's this for a solution:

I've grouped each session per person based on IN/OUT, If you want total time worked per person, then you can get that from the offshoot at the end:

Hope that helps,

Ollie

[Bobbins]

Hi All,

Apologies, should have included more data. @grazitti_sapna  so now attached

@OllieClarke  That almost works but when i try it with the dummy data it falls apart on some rows?

[grazitti_sapna]

@Bobbins, is this what you are looking for?

I hope this helps!

Thanks!

[OllieClarke]

Hey @Bobbins 

the reason a couple of rows don't work properly is that there are instances where someone has checked in, but not out (or vice versa)

Will that be possible in your actual data set?

[Bobbins]

@grazitti_sapna  Almost perfect, although there is the problem that @OllieClarke correctly picks up on. So yes, this is real time/dates just name and numbers changed. It occurs because most likely this individual has incorrectly exited through a turnstile (IE some of them are large swing gates and although you should correctly do it one at a time, obviously some people dont!)

In addition, because we will look at this data in a non continous snapshot kind thing, some people will be seen as leaving or exiting without the other occurring. Not sure how to count this out really and hence I am not sure how to solve this, the below highlights the problem using @grazitti_sapna  answer.

I dont think there is a suitable answer, if not, will mark the above as the correct answer. Thank you

[grazitti_sapna]

@Bobbins, thanks for notifying I have made the changes and please take a look if this workflow solves your purpose.

Thanks!