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

Options

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!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!