Combining dates with overlapping ranges

Question

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 dateEnd DateDec 2019Aug 2021Jun 2019Feb 2021Sep 2019Dec 2019Jun 2019Sep 2019Mar 2017Jun 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.

MRoyW · Answer

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

MRoyW · Answer

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 · Answer

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?