Sum of subintervals without counting gaps nor double counting overlaps

Hi!

I have start and stop datetimes in cells A19 and B19, respectively.

I also have a spill range in E3# with various other start datetimes and a spill range in F3# with various other stop datetimes.

All these start and stop times form intervals/durations.

I need a formula that identifies the parts of the intervals in E3# to F3# that falls within the borders of A19 and B19 [(E3# <= B19) * (F3# >= A19)] and which sums up those subintervals without summing gaps between these subintervals and without counting overlapping time between these subintervals more than once.

A solution that can work with the single cell input A19 and B19 would be good, but I also need a formula that can handle spill ranges as input; A19# and B19#, and create a spill range as output.

I have attached a workbook with sample data and two formulas I have tried that fails to handle gaps and overlaps correctly.

Thanks in advance for any suggested solutions.

Kind regards,
Marbleking

Thank you for this, DJunqueira! I'll dig more into your suggestion this morning. There may be some great tips there.

I have updated my workbook with a graphic representation of what I am after. I hope for a single formula that can calculate this from the spill ranges in Section 2.

Regards,
Øystein