Calculate a duration from times that have gaps and overlaps
I have this data of start and stop times. I want to calculate the duration of the task for each person. But overlapping times don't count. Calculating for some people is easy. If they have no overlaps, it could be the sum of the durations. For some that overlap, I could take the min start to the max end.
But there can be an arbitrary number of data points, and there can be any number of overlapping times and any number of gaps. What can I do for the duration for the more complicated ones? And of course I want one formula for the whole column. Thanks for any ideas.
All times are on the same day. They are actually datetimes, just displayed as times, so even if they were not on the same day, any math your come up with would work correctly.
Name Start End Duration
Person 1 6:39 PM 7:02 PM
Person 1 8:02 PM 8:10 PM
Person 2 6:32 PM 9:08 PM
Person 3 6:25 PM 7:02 PM
Person 3 6:32 PM 9:06 PM
Person 3 7:02 PM 8:13 PM
Person 4 7:01 PM 7:59 PM
Person 5 6:47 PM 8:43 PM
Person 5 8:43 PM 8:54 PM
Person 6 6:45 PM 9:08 PM
Person 7 7:02 PM 8:12 PM
Person 7 7:17 PM 7:20 PM
Person 8 6:56 PM 8:13 PM
Person 9 6:32 PM 8:55 PM
Person 9 6:32 PM 8:52 PM
Person 10 6:38 PM 8:55 PM
ETA: Expected output
Name Duration
Person 1 0:31
Person 2 2:36
Person 3 2:41
Person 4 0:58
Person 5 2:07
Person 6 2:23
Person 7 1:10
Person 8 1:17
Person 9 2:23
Person 10 2:17
[link] [comments]
Want to read more?
Check out the full article on the original site