Does a range of dates fall between another range of dates?

samanthabooty

Hello,

I have a need to be able to highlight if a range of dates falls between any other range of dates.

I have a checkbox column which needs to be ticked if a range of dates conflicts with other range of dates.

So for example, we might have a number of projects running throughout the year across different clients and there might be an occasion where there is an office shutdown, or a change freeze of a product which means we need to know if this clashes with any organised projects.

So when a new 'downtime' date range is added, it checks the checkbox to let us know that it clashes with an existing project.

Please see the screenshot attached to see the layout required.

In this example I need Project 1 and 3 to be checked as the downtime clashes with both of these projects.

Does anyone know if there is a formula I can use to auto-check the box?

Andrée Starå

Hi Samantha,

Try this.

The formula below will use row 1 for the conflict checkup so to handle multiple dates it needs to be updated.

=IF(AND($[Start Date]$1 < [End Date]@row; $[End Date]$1 >= [Start Date]@row); 1)    

The same version but with the below changes for your and others convenience.  

=IF(AND($[Start Date]$1 < [End Date]@row, $[End Date]$1 >= [Start Date]@row), 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

samanthabooty

Thanks Andrée,

That works great.

Best,

Samantha

Andrée Starå

Happy to help!

Glad to hear that it's working!

Best,

Andrée

Lauren Mullane

Hi Andree,

What if you want a checked "conflict" box when there is an overlap between any dates in the sheet? In Samantha's example above, there is an overlap between clients 2 & 3 in the month of March and a conflict with the downtime described.

Thanks,

Lauren

Paul Newcome

@Lauren Mullane

HERE is a post that answers how to check for overlap against multiple date ranges.

Andrée Starå

@Lauren Mullane It seems like Paul answered your followup question.

Happy Holidays!

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!