Check to see if two values fall within the same date range

Alex Berry

Hi,

I am trying to build a schedule tool using a gantt chart as a base. I need to check to see if a piece of equipment is being scheduled within the same date range but I cannot seem to figure out the formula (if this is even possible).

E.g. Item 1 has been scheduled for use between 2/9/21 to 2/11/21 but Item 1 has also been scheduled for use between 2/10/21 and 2/15/21.

Below is a screenshot of my layout:

In the screenshot, I would like to flag Item 2 as having a conflict because it has been scheduled for use between 2/15-2/26 and 2/15-2/18.

I have been able to identify where the equipment appears more than once and when any date ranges overlap but I can't seem to figure out how to only check the dates applicable to the Item, so per the screenshot, my current formula is flagging Item 1 as having a conflict because it appears twice and shares an overlapping date with Item 2 (2/15/21). Obviously, I don't care that Item 1 and Item 2 are scheduled for use on the same day.

=IF([Equipment Name]3 = "", "", IF(COUNTIF([Equipment Name]:[Equipment Name], [Equipment Name]@row) > 1, IF(COUNTIFS([End Date]$3:[End Date]$7, >=[Planned Start Date]@row, [Planned Start Date]$3:[Planned Start Date]$7, <=[End Date]@row) - 1 > 0, 1)))

If anyone can help on this, I'd really appreciate it.

Thanks!

Paul Newcome

Move the range/criteria for the equipment name COUNTIF and combine it in the COUNTIFS where you are checking dates.

COUNTIFS([Equipment Name]:[Equipment Name], [Equipment Name]@row, [End Date]$3:[End Date]$7, >=[Planned Start Date]@row, [Planned Start Date]$3:[Planned Start Date]$7, <=[End Date]@row)

Paul Newcome

Move the range/criteria for the equipment name COUNTIF and combine it in the COUNTIFS where you are checking dates.

COUNTIFS([Equipment Name]:[Equipment Name], [Equipment Name]@row, [End Date]$3:[End Date]$7, >=[Planned Start Date]@row, [Planned Start Date]$3:[Planned Start Date]$7, <=[End Date]@row)

Alex Berry

Hi Paul,

I can't believe this was so easy! I spent ages on this!

Worked like a charm. The only thing extra I had to do was extent the date ranges to cover the whole column for start and end date, otherwise I was getting an incorrect argument set.

Thanks for your help!

Paul Newcome

Happy to help. 👍️

Sorry about the mix-up. I just copy/pasted from your original post and didn't even notice the difference in ranges.