How to calculate hours worked in a day excluding overlap hours

[India User]

Hi,

I have few records with start and End times of each resource. One resource can have multiple start and end times since he is working on multiple tasks.

If the user have overlapping hours [ i.e. if he is doing multiple tasks at the same time ] I do not need those hours. I need the exact actual hours the person was working in a day.

i.e

Emp Task start end Actual hrs worked in a day
Peter Task1 10-01-2017 10:00 10-01-2017 13:00 03:00
Peter Task2 10-01-2017 10:30 10-01-2017 14:00 01:00
Peter Task3 10-01-2017 11:30 10-01-2017 13:30
Peter Task4 10-01-2017 15:00 10-01-2017 17:00 02:00


As you can see we have to consider only 1 hr for Task2 [13:00 to 14:00 ] since I have already considered time till 13:00 in task1.


we have to consider only 0 hr for Task3 since I have already considered time from 11:30-13:30 in task1


Infact I need the 'Actual hrs worked in a day' per resource in a single cell. What formula should I apply. Please help.


File attached for your reference.

 

[vletm]

@India User
If something else than 'formula' would be okay
then let me know ...

 

[India User]

yes. All options are welcome. I need a solution..

 

[vletm]

Press [Do It]-button
and
You would get values.
'My Qn' layout have to be like in Your sample!
There could be something
... but with this data it works!
> Ideas?

 

[India User]

Thank you very much for the quick support. Reviewing the VB script.