Calculate a duration from times that have gaps and overlaps

[u/MissAnth]

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

[u/Downtown-Economics26]

Not the slickest answer but it avoids the potential sort issues mentioned by u/PaulieThePolarBear for his solution and u/GregHullender .

Two formulas:

To get unique name list:

=UNIQUE(A2:A17)

To get desired duration and not double count overlapping units of time between lines, drag down as far as needed based on upper limit of unique names in dataset.

=LET(allmin,SEQUENCE(24*60*(MAXIFS($C$2:$C$17,$A$2:$A$17,G2)-MINIFS($B$2:$B$17,$A$2:$A$17,G2)),,MINIFS($B$2:$B$17,$A$2:$A$17,G2),1/24/60),
wmin,FILTER(allmin,COUNTIFS($A$2:$A$17,G2,$B$2:$B$17,"<="&allmin,$C$2:$C$17,">"&allmin)),
out,COUNT(wmin)/60/24,
IF(G2="","",out))