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).

Example of guide schedule conflict.png


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.
    Jeff M. ✭✭✭
    Answer ✓

    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.
    Jeff M. ✭✭✭
    Answer ✓

    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.
    Jeff M. ✭✭✭
    Answer ✓

    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".

  • 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.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.
    Jeff M. ✭✭✭
    Answer ✓

    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.

  • Thank you so much@Jeff M.!! This worked great, and is a much more elegant solution than using a monster formula like we were. And as a bonus, my schedulers can click into the row from the Calendar App if they need to see who the conflict warning is for.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/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":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","snippet":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it…","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"

Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>

=IF([Month Column]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>

Replace [Month Column] with your column name.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions