Omitting overlapping connection times?

[jwlam]

Hi everyone,

I'm struggling to get this formula to work. I need to calculate the total duration people attended a meeting. However, the report shows overlapping times and includes the duration of those. Is there a way to omit rows with overlapping connection times so I can get a column with Actual Duration? Maybe with a macro?

End goal is to only have non-overlapping times to be counted so for John Smith that last row shouldn't be 8 since the times overlap with his first row.

Thanks!!

[Yoshiro_Fujimori · Accepted solution]

Hi @jwlam ,

My solution below may be somewhat brute force, but it works if the duration is not too long.

I hope it works.

Workflow

The idea is to generate one row by each second between Start Time and End Time, and count distinct the rows by each ID.

[Qiu]

@jwlam 
I am not able to fully understand your intention here.

So take instance of John Smith, we will remove #11-#14 since their time is having overlapping with row#10, given that we sort the data of Start Time in an accedening order?

[jwlam]

@Yoshiro_Fujimori brute force works for me. Thanks!!