Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Creating a fictious period of time without overlapping periods of time

Copper Contributor

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.



3 Replies

BonoTenere_0-1676138562803.png

This is an example of the format in Excel, I can not upload a test file unfortunately.

@BonoTenere 

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))))

 

@BonoTenere 

 

I hope the next formula helps you. you have to analize the overlaping dates and substract from the result

CFA7F191-8AA9-417B-BD8F-3B14BAB525FD_4_5005_c.jpeg