Identify matching apps with date conflicts and print out "conflict"

The general idea is I have a sheet where we list each effort and the application it impacts (column "Application") as well as the dates the Code is going to be in my QA environment. I'm trying to create a formula that checks when the "Application" values match and if so compares my date range [code drop date to end prod support date] and prints "Conflict" or something where i have overlapping efforts

I tried

=IF(COUNTIFS(Application:Application, Application@row, [Code Drop Date]:[Code Drop Date], "<=" & [End Prod Support]@row, [End Prod Support]:[End Prod Support], ">=" & [Code Drop Date]@row) > 1, "Conflict", "No Conflict")

But i'm getting #unparsable, it's my first attempt building a formula in smartsheet and looking for advice

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

It looks like you are using Excel syntax possibly. Try this instead:

=IF(COUNTIFS(Application:Application, Application@row, [Code Drop Date]:[Code Drop Date], <= [End Prod Support]@row, [End Prod Support]:[End Prod Support], >= [Code Drop Date]@row) > 1, "Conflict", "No Conflict")

Ainnise

I tried the syntax correction you provided but I'm still getting #unparsable to try and better explain:

I have a column called "Applications" it's a drop down/list selection from a list we created, for my sheet I also have 2 columns considered the start and end, which are date columns. I would like to have a cell formula compare the current row value of "Applications" and only where the rows match compare the row start and end dates for each (or all) to ensure that the start date of one doesn't fall prior to the end date of any other.

Paul Newcome

I understand exactly what you are hooping to accomplish, and the formula syntax above is exactly what you need. Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it.

Ainnise

Yes, Here u go

Paul Newcome

Double check the column names in the formula match exactly what you have in the sheet. Open up the column properties even to ensure they don't have any hidden spaces or anything.

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.