Omitting overlapping connection times?

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!!

New Workflow3.yxzp

Datasets

Transformation

Accepted answers

Yoshiro_Fujimori

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.

Community_1195247.yxmd

All comments

Yoshiro_Fujimori

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.

Community_1195247.yxmd

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?

2023-10-07 095230.png

jwlam

@Yoshiro_Fujimori brute force works for me. Thanks!!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335