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!

Find more posts tagged with

Accepted answers

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)

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.