Getting a shift time with login/logout data

Hello,

I want to determine the start of a shift and the end of a shift for one person per day. The shifts could be a 3rd shift. Here is some sample data.

Example:

Login	Logout
7/18/2023 19:55	7/19/2023 5:10
7/18/2023 2:35	7/18/2023 5:04
7/17/2023 19:49	7/18/2023 2:30

In this example, one shift would be from 19:49 - 5:04 starting on 7/17/23 and ending on 7/18/23 and another shift was from 19:55 - 5:10 starting on 7/18/23 and ending on 7/19/23.

The amount of rows per shift is unknown and never a set number.

I've tried to make some magic happen with multi-row formula's but i haven't been 100% successful because of all of the possible variables.

Any ideas?

Datasets

Accepted answers

All comments

rzdodson

@jordanmount6 maybe something like this?

Solution.yxzp

jordanmount6

That's very similar to what i first came up with but once i add more unique users to the data, it doesn't work. There are around 1000 users with shift data for the last year. Any other ideas?

jordanmount6

Here is some larger sample of data with 10 unique users. @rzdodson

Sample Data.yxdb

rzdodson

@jordanmount6 taking a look at your latest workflow. Will circle back soon.

Edit: A couple of items for you.

1. There are 415 duplicate records where the LOGID/LOGIN/LOGOUT combination occur multiple times in the data set provided. Is this intentional?
2. How much of a break should I account for? It looks like in some instances, there is about a 4-8 minute difference between their first logout time and the second login time, while in other (more obvious) instances, there are clear instances of it being different shifts entirely (separated by >480 minutes (8 hour break between different shifts).

jordanmount6

Hey

1. Duplicates can be removed, its just raw data that i didnt clean up yet

2. I was going to go with > 540 minutes because i did see some instances of a long shift with no breaks. As an example of one that i saw, they were logged in for 9 hours then logged out for 5 minutes and logged back in for 10 more minutes.

rzdodson

@jordanmount6 try this out. Should be close.

Solution.yxzp

jordanmount6

@rzdodson This is very very close. On the datetimediff function, what does it do for the minutes piece if its many days apart but the minutes are within 540?

Example:

2023-09-20 12:25:37
2023-10-23 11:50:21

It kept it within the same shift, im assuming because of the time itself and not the date?

rzdodson

The way that the DateTimeDiff formula functions here is to calculate the number of minutes between the Login time and the Row-1 Logout. If it is greater than 540 minutes, the workflow treats it as a unique shift unto itself. It does not matter if the dates are different and the times are the same. It'll calculate the total number of minutes between those two Date/Times.

Using the example below, it'll take the 2023-10-23 11:50:21 time, subtract the 2023-09-20 12:25:37, convert that into minutes, then the conditional statement of whether that number is greater than or equal to 540 takes over from there.

What row numbers were you seeing that issue in Sort (9) from the workflow I attached? I'll take a look again for you.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703