Overlapping Date Range Report

[chadosu]

Hello experts,

I am looking to generate a report that produces overlapping date ranges of projects within a group.  I have seen similar topics, but not one that handles 2 different sets of overlaps within a group like this example.

Data
Components	Projects	Start Date	End Date
IT Component 1	Project A	10/1/2024	10/1/2025
IT Component 1	Project B	1/1/2024	12/31/2024
IT Component 2	Project C	1/1/2024	1/31/2025
IT Component 2	Project D	9/15/2024	10/1/2025
IT Component 2	Project E	7/15/2024	1/15/2026
IT Component 2	Project F	11/15/2025	1/15/2026

Desired Result
Components	Projects	Overlap Start	Overlap End
IT Component 1	Project A	10/1/2024	12/31/2024
	Project B
IT Component 2	Project C	7/15/2024	1/31/2025
	Project D
	Project E
	Project E	11/15/2025	1/15/2026
	Project F

Thank you in advance for any help on this.

[collinmarchese]

Here's a primitive example of how to achieve this. If i had more time I would figure out a way to make this more dynamic but this will give you an idea. Basically creating record for each date between start/end range and joining on dates between the projects to see the overlapping dates, then find min/max of the shared dates. hope this helps.

[chadosu]

I appreciate your input!  I have been able to get close with the attached file.  My problem is this takes the absolute start and end of Component 2 instead of breaking it apart.

[Pang_Hee_Choy · Accepted solution]

@chadosu here my version.

mainly use join for component, then each project is compared to all project within same component. 

then use math to reveal the overlap. 

 

and it was hard to merge as you can see project D actually fully overlap by project E. Hence, I output everything.

beside, for your workflow, you can add one multi-row formula, if the date different more than 1 then goto another group then summarize with ID.

config:

result:

[chadosu]

I believe this is the solution I am after! I realized that in my original desired result, not all overlaps were actually captured, but your solution captures them!

Thank you very much!