Check to see if two values fall within the same date range
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
-
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)
Answers
-
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)
-
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!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!