Checking for conflicting assignment dates across two columns
Long time reader, first time poster--this one has a few people stumped.
I have a variety of events with Start Dates & End Dates. Staff members are assigned as a 1st Guide or 2nd Guide (its critical we differentiate which role they have been assigned).
I am looking for a way to pop a warning symbol when one of the names is assigned to conflicting events. The challenge has been that we need to look for conflicts within "1st Guide", conflicts within "2nd Guide", AND conflicts between the two.
This formula seems to work for checking for conflicts within a single column:
=如果((ISBLANK([1日指南]@row)), 0,如果条件统计([1 st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1, 0))
It's the method for cross-checking the two columns that we can't find a solution for--by formula, helper sheet, whatever might work.
Huge thanks to anyone who can crack this! It will make a lot of people's lives much easier, this will allow us to use the Calendar App directly to schedule.
Best Answers
-
Jeff M. ✭✭✭
I think I solved it for you!!!!!!
I created a column named "1st Guide Flag" and plugged in this formula.
=如果((ISBLANK([1日指南]@row)), 0,如果条件统计([1 st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([1st Guide]@row)))
Then another column named "2nd Guide Flag" with this formula
=IF((ISBLANK([2nd Guide]@row)), 0, IF(COUNTIFS([2nd Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([2nd Guide]@row)))
Then a final column for cross checking "Cross Check Flag"
=IF(COUNTIFS([1st Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:A, @cell <= [End Date]@row, [End Date]:C, @cell >= [Start Date]@row) = 1, JOIN([1st Guide]@row:[2nd Guide]@row))
这些列s will return the names in conflict.
The warning symbol could be accomplished by setting up conditional formatting that will highlight the 1st Guide or 2nd Guide cell when the corresponding "Flag" column "is not blank".
-
Jeff M. ✭✭✭
Oh yes, I forgot to add that part in my initial response. The reason for having the letters was that I created new helper columns and named them A, B, C. I used them to troubleshoot and correct the error messages that were returning with the cross flag formula. When using the COUNTIFS formula, the range of columns throughout the formula need to be equal. Since the cross check formula was evaluating both 1st guide & 2nd guide columns, I added helper columns that would add to each Date column (e.g. [Start Date]:[Helper Column A] & [End Date]:[Helper Column B])
Also, you'll want to make sure the "helper columns" are directly next to the other date column so the column range is valid. Once the column is added, you can select to hide the helper columns right away.
Answers
-
Jeff M. ✭✭✭
I think I solved it for you!!!!!!
I created a column named "1st Guide Flag" and plugged in this formula.
=如果((ISBLANK([1日指南]@row)), 0,如果条件统计([1 st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([1st Guide]@row)))
Then another column named "2nd Guide Flag" with this formula
=IF((ISBLANK([2nd Guide]@row)), 0, IF(COUNTIFS([2nd Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([2nd Guide]@row)))
Then a final column for cross checking "Cross Check Flag"
=IF(COUNTIFS([1st Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:A, @cell <= [End Date]@row, [End Date]:C, @cell >= [Start Date]@row) = 1, JOIN([1st Guide]@row:[2nd Guide]@row))
这些列s will return the names in conflict.
The warning symbol could be accomplished by setting up conditional formatting that will highlight the 1st Guide or 2nd Guide cell when the corresponding "Flag" column "is not blank".
-
Kevin T ✭
I think you're onto something here! I'm just not quite not following what "A" and "C" are referring to in the "Cross Check Flag" formula, I think I need to get my head wrapped around that in order to make it work.
... [Start Date]:A, @cell <= [End Date]@row, [End Date]:C...
But this promises to be much more elegant than a single monster formula!
-
Kevin T ✭
@Jeff M.Oops, forgot to tag you in my response above--reposting here
I think you're onto something here! I'm just not quite not following what "A" and "C" are referring to in the "Cross Check Flag" formula, I think I need to get my head wrapped around that in order to make it work.
... [Start Date]:A, @cell <= [End Date]@row, [End Date]:C...
But this promises to be much more elegant than a single monster formula!
-
Jeff M. ✭✭✭
Oh yes, I forgot to add that part in my initial response. The reason for having the letters was that I created new helper columns and named them A, B, C. I used them to troubleshoot and correct the error messages that were returning with the cross flag formula. When using the COUNTIFS formula, the range of columns throughout the formula need to be equal. Since the cross check formula was evaluating both 1st guide & 2nd guide columns, I added helper columns that would add to each Date column (e.g. [Start Date]:[Helper Column A] & [End Date]:[Helper Column B])
Also, you'll want to make sure the "helper columns" are directly next to the other date column so the column range is valid. Once the column is added, you can select to hide the helper columns right away.
Help Article Resources
Categories
<\/p>
When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":53,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"
You should be able to use this formula to accomplish this:<\/p>