Date time overlapping hours, how to detect and sum duration of overhang and date time

[thoe0059]

Hi, 

When two tasks are carried out concurrently by the same employee doing the same task on the same place, but the start or end is different, i need to find out how much overhang there is (in hours).

I have added an example of the data, here the first bug fix is from 11:00 to 18:00 

But in between another bugfix is carried out from 9:00 to 12:00

Here the total should be the period from 9:00 to 18:00 (9 hours) minus the two hours where these tasks overlap (In total that would be 7 hours). 

Can you make a formular to sort this out? The attached is only a sample, the real data is 1000+ rows. 

I hope my explanation makes sense, and I hope to hear from someone on this great community. 

SAMPLE

Place	Employee	Task	StartTime	EndTime	Duration
Office	Person 1	Cleaning	15-04-2020 07:50	15-04-2020 12:35	4.75
Office	Person 1	Cleaning	16-04-2020 08:50	16-04-2020 11:00	2.17
Office	Person 1	Bug fix	16-04-2020 11:00	16-04-2020 18:00	7
Office	Person 1	Bug fix	16-04-2020 9:00	16-04-2020 12:00	3
Office	Person 3	Bug fix	16-04-2020 14:50	16-04-2020 18:00	3.17

Kind regards, Thøger

[Luke_C]

@thoe0059 

There's a weekly challenge that does exactly this! If you look at some of the solutions there you should get what you need:

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-106-How-Long-were-the-Lights-On/td-p/142...