Forum Discussion
Creating a fictious period of time without overlapping periods of time
Dear,
For a large project I am experiencing some trouble in managing to create a fictious period of time based on several rows of dates. I manage to create the period manually or when the period of time does not overlap. However, In my results there are large periods of time that do overlap. For example:
These periods are easy to calculate:
1: 1-1-2023 - 31-3-2023
2: 1-6-2023 - 30-7-2023
3: 1-12-2023 - 31-12-2023
The fictious period should count up to 6 months -> 1-1-2023 - 30-06-2023.
In the next example there will be some overlap:
1: 1-1-2023 - 31-3-2023
2: 1-3-2023 - 30-6-2023
3: 1-12-2023 - 31-12-2023
The fictious period should count up to 7 months -> 1-1-2023 - 31-07-2023. But if I use a formula to calculate the first example, it will count the period between 1-3-2023 - 31-3-2023 twice. Resulting in a fictious period of 8 months instead of 7.
So I am looking for a formula that could give a fictious period based on the given periods, or the amount of days without any overlapping dates. I can not find an easy to implement formula for my problem, I hope that anyone has some tips/tricks to help me. If it would take multiple steps/formulas it would also be no problem.
Many thanks in advance.
This is an example of the format in Excel, I can not upload a test file unfortunately.
I hope the next formula helps you. you have to analize the overlaping dates and substract from the result
A somewhat unusual approach on my part. First put all the days in a list and then delete the duplicates or multiples and then count them.
=COUNT(UNIQUE(VSTACK(SEQUENCE(B2-A2+1,1,A2),SEQUENCE(D2-C2+1,1,C2),SEQUENCE(F2-E2+1,1,E2))))
