Hi,
I have a dataset which contains time periods by individual users - sample below. What I am looking to do is identify the amount of time that periods overlap for a specific user. For example, I would like to know that user 001 has an overlap/duplicate time of 3:30hrs on 1/1/2021.
User ID | Date | Start time | End Time |
001 | 1/1/2021 | 08:00 | 12:00 |
001 | 1/1/2021 | 08:30 | 12:00 |
002 | 1/1/2021 | 08:00 | 12:00 |
I have been looking at this post about overlapping timestamps which is very similar to my situation, except for the user ID part, the solution there helps you find any overlap between different users but I want the exact opposite of that and when I summarize I lose the user ID granularity.
My first thought was to add user ID to the "formula" that combines start time and start date (eg. 001 1/1/2021 08:00) and generate rows like that but that obviously won't work since down the line it does time comparisons and you can't combine strings with date/time.
I looked at the solution from this post about finding concurrent date ranges so I put the data into the following format with one row per user and all the times in columns:
User ID | Date | Start time 1 | Start time 2 | End time 1 | End time 2 |
001 | 1/1/2021 | 08:00 | 08:30 | 12:00 | 12:00 |
002 | 1/1/2021 | 08:30 | 12:00 |
But then when I have to calculate the part below as described in the post, I have up to 10 start/end time entries for some users and am unsure how to compare each time when there are different amounts of entries by each user in a day (some have 1-2, others have many more)
IF (DateTimeDiff([Begin2],[Begin1],"seconds") > 0 && DateTimeDiff([Begin2],[End1],"seconds") < 0) || (DateTimeDiff([End2],[Begin1],"seconds") > 0 && DateTimeDiff([End2],[End1],"seconds") < 0) THEN 1 ELSE 0 ENDIF
What would the wise folks of this forum advise?
Much appreciated!
Solved! Go to Solution.
Hi @Jake_NG ,
My solution is very similar to @BenMoss in the post you linked here: overlapping timestamps
The main difference is that I added 'UserId' as a groupby in the summarize tool and added a couple of formulas specific to your use case.
Let me know if this does/doesn't work for you and if you have any questions!
@Jake_NG
Maybe I am missing something?
Wow, amazingly prompt response! I will give it a shot and report back. This community is great.
Kelsey that worked - thank you so much! The only tweak I had to make was to change the final formula to be
IF [Count]>1 THEN [count] ELSE 0 ENDIF
I was interested in counting any overlap at all so that made if there were more than 2 overlaps, it would count the third since in the original it would discount any duplicate if it was present more than once. Much appreciated!