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 ProjectsStart DateEnd DateIT Component 1Project A10/1/202410/1/2025IT Component 1Project B1/1/202412/31/2024IT Component 2Project C1/1/20241/31/2025IT Component 2Project D9/15/202410/1/2025IT Component 2Project E7/15/20241/15/2026IT Component 2Project F11/15/20251/15/2026 Desired Result ComponentsProjectsOverlap StartOverlap EndIT Component 1Project A10/1/202412/31/2024 Project B IT Component 2Project C7/15/20241/31/2025 Project D Project E Project E11/15/20251/15/2026 Project F Thank you in advance for any help on this.

Overlapping Date Range Report

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.

Datasets

Date Time

Help

Accepted answers

AncientPandaman

@chadosu here my version.

Screenshot 2023-08-16 103439.png

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

then use math to reveal the overlap.

Screenshot 2023-08-16 103434.png

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

Screenshot 2023-08-16 103455.png

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:

Screenshot 2023-08-16 103246.png

result:

Screenshot 2023-08-16 103300.png

Overlap.yxmd

All comments

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.

Overlapping Date Range Report.yxmd

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.

Overlapping Date Range Report.yxmd

AncientPandaman

@chadosu here my version.

Screenshot 2023-08-16 103439.png

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

then use math to reveal the overlap.

Screenshot 2023-08-16 103434.png

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

Screenshot 2023-08-16 103455.png

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:

Screenshot 2023-08-16 103246.png

result:

Screenshot 2023-08-16 103300.png

Overlap.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703