Combining dates with overlapping ranges

[MRoyW]

Hi, I have a workflow that basically looks at two streams of dates and then outputs the total no. of years and months.

Format:

Start date	End Date
Dec 2019	Aug 2021
Jun 2019	Feb 2021
Sep 2019	Dec 2019
Jun 2019	Sep 2019
Mar 2017	Jun 2019

My challenge is like for the example in the table above, some of the dates can overlap each other. For example, a part of the time in the second row is included in the first.

Does anyone know how I can account for such date ranges such that I only get the common subset in my total no. of years and months rather than overlapped time?

Thanks in advance.

[atcodedog05]

Hi @MRoyW 

Its bit confusing can you provide us the expected output to get a better understanding.

[RolandSchubert]

Hi @MRoyW ,

you can convert your dates to Date data types using a Formula tool and the function DateTimeParse([Field], '%b %Y'), use a Summarize tool to find the MINIMUM for the [Start] date field and the MAXIMUM for the [End] date field and convert the result back to the format you need.

I've attached a sample workflow, hope this is helpful.

Best,

Roland

[MRoyW]

Hi @RolandSchubert I appreciate this. However, this would take the continuous time range from the minimum start date to the last end date. What if there are breaks in between?

[MRoyW]

Hi @atcodedog05, as a guide, please see sample output before summarising the years and months. Basically, Time diff is the difference between the dates/ 12. Month_part  is the decimal component of the time diff converted into months, Years and Months are the Time diff and Month_part rounded to the nearest 1.


After this, the years and months can be summarised. 
The challenge is: what if some of the date ranges overlap each other? Ideally, I would like to handle like {a,b,c,d} U {a,b} = {a,b,c,d} and not {a,a,b,b,c,d} as would be the case with current calculation.

Does this make it clearer?

[MRoyW]

For better accuracy I changed the years calculation to Floor(Time diff)