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
Bookmarks