Identify matching apps with date conflicts and print out "conflict"
![Ainnise](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
Answers
-
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")
-
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. -
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.
-
Yes, Here u go
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!