Hello, I'm currently working on a spreadsheet that calculates the amount of time people spend doing something but I'm struggling with the calculation because of time overlap. In the attached spreadsheet you'll see multiple names with times, every name is a mixture of paint that is being mixed and to the right of the name the time from start to end on each mix. But some of the mixes overlap each other and I need to be disregard the time spent on overlap.
So eg. David 9:00 - 10:00 (This is 60 minutes)
eg.2 David 9:10 - 10:05 (Only 5 minutes will be added)
I only add 5 minutes in eg.2 Because the person was already busy from 9:00, and basically ended at 10:05.
I hope I'm being clear with my examples. I would really like to find some kind of formula that can help me with this, as I have been doing it manually with pen and paper which could end up being Inaccurate and very time consuming.
I need the formula under Total Hours.
TS -Time started
TE -Time ended.
Thank you! :D
-Edit- I have Tried a Min and Max formula, the way I use it will not work in this scenario, let me explain.
eg.1 David does one mixture of paint from 9:00 - 10:00
David does another mixture of paint from 9:00 - 10:05
David does his 3rd mixture from 12:00 - 14:00
Max would be 14:00, Min would be 9:00. Saying he spent 5 hours mixing is false, as there was a break in time from 10:05 until 12:00.
Really need help guys, hope this edit helps a little.
Bookmarks