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
There's a weekly challenge that does exactly this! If you look at some of the solutions there you should get what you need: