How to calculate sum hours/day by taking into overlapping periods in Excel

Question

I need to calculate the sum of hours/per day taking into account overlapping periods in excel

---

For example:

Task Created                Resolved
1    2020-01-03 09:43:50    2020-01-03 11:42:22
2    2020-01-03 08:09:59    2020-01-03 17:04:51

Sum of hours for these 2 tasks should be 8hr54 mins instead 10hrs 52 mins Could you please help

Answer 1

First format the column for time lapse. Then add this to your first cell D1

 =C1-B1

Then copy/paste this the rest of the way

 =sum(if(Datevalue(B2)-Datevalue(B1)=0,sum(if(B2-C1<0,C2-V1,C2-B2),D1),C2-D2))

Answer 2

You can try following formula:

=IF(SUMPRODUCT((B2<$C$2:$C$7)*(INT(B2)=INT($C$2:$C$7))),AGGREGATE(14,6,$C$2:$C$7*(INT(C2)=INT($C$2:$C$7)),1)-AGGREGATE(15,6,(1/(INT(B2)=INT($B$2:$B$7)))*$B$2:$B$7,1),C2-B2)