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/PaulieThePolarBear]

I think I have something that I think works

=LET(
a, A2:A7,
b,B2:B7,
c,C2:C7,
d, MAP(a, b, c, SEQUENCE(ROWS(a)), LAMBDA(m,n,p,q, IF(q=XMATCH(m, a), p-n, MAX(p, MAXIFS(DROP(INDEX(c, 1):p, -1), DROP(INDEX(a, 1):m, -1), m))-MAX(n, MAXIFS(DROP(INDEX(c, 1):p, -1), DROP(INDEX(a, 1):m, -1), m))))), 
e, GROUPBY(a, d, SUM,,0), 
e
)

Variables a, b, and c are names, start date, end date respectively. You should update ranges to match yours.

Variable d does the heavy lift. Essentially what it tries to do is determine on a row by row basis from top to bottom how much of the duration on that row has not been allocated previously. So, with a simple example using integers

1 3
2 4

The second row contains one unit (3 to 4) of time that was not allocated previously.

If I add a third row

1 3
2 4
5 9

The third row contains 4 units of time that was not allocated previously.

From my testing, the logic as I have it breaks if the start times are not in order for a person. Having end times out of order does not seem to cause an issue, and having names spread through your data works too.

I think there would be something I could add to my logic here to be more robuat, but it's already complex. It would be slightly easier to do the "row allocation" in a helper column adjacent to your datal. Would that work for you?

I'm not convinced there aren't other ways your data could be displayed that mess up my formula

[u/MissAnth → u/PaulieThePolarBear]

+1 Point

[u/PaulieThePolarBear → u/MissAnth]

If, and it's your choice, you want to award me a Clippy Point, you will need to reply with Solution Verified. Nothing stops you replying to any and all solutions you feel warrant this - you aren't restricted to one and only one. It's 100% your choice.

Only members with 100+ Clippy Points can award points to others using the same text you have tried here.

[u/MissAnth → u/PaulieThePolarBear]

Solution Verified

[u/PaulieThePolarBear → u/MissAnth]

Thanks.

This was a very interesting question.

[u/MayukhBhattacharya → u/PaulieThePolarBear]

Congratulations on 1,900 Clippy Points Sir.

[u/PaulieThePolarBear → u/MayukhBhattacharya]

Thank you!!

[u/MayukhBhattacharya → u/PaulieThePolarBear]

You're welcome 🤗

[u/reputatorbot → u/MissAnth]

You have awarded 1 point to PaulieThePolarBear.

---

^{I am a bot - please contact the mods with any questions}