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!
