Multiple Check Boxes - Pre-Fix, joining values based on multiple check boxes.

Jimrny
Jimrny ✭✭
edited 08/29/22 inFormulas and Functions

What would be the best formula to join "text" from multiple check boxes if values are true or false?

I would like to populate, the in the grey column, the values of each check box if any are true with a "text value" of the name of the check box column.

image.png


I have several grey columns that will be used in with join/collect on another sheet.

image.png


I've tried this, but seems to be incorrect:


=JOIN(IF(Response@row = 1, "Response", IF(Reoccurring@row = 1, "Reoccurring", IF(Technician@row = 1, "Technician", IF(Set-Up@row = 1, "Set-Up", IF(Equipment@row = 1, "Equipment", ""))))),", ")

· Share on Facebook Share on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this one:

    IF the COUNTIFS of checkboxes is 1 then SUBSTITUTE the comma with a blank, otherwise run the full string.


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1,SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""))

    10xViz.com

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Give this a go...


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) > 0, "MECHANICAL - " +IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")))

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would actually write out separate IF statements and "add" them together with the delimiter.


    =IF(Response@row = 1, "Response, ") + IF(Reoccurring@row = 1, "Reoccurring, ") + so on and so forth...

    10xViz.com

    · Share on Facebook Share on Twitter
  • Jimrny
    Jimrny ✭✭
    You would actually write out separate IF statements and "add" them together with the delimiter.<\/p>

    =IF(Response@row = 1, "Response, ") + IF(Reoccurring@row = 1, "Reoccurring, ") + so on and so forth...<\/p>","bodyRaw":"[{\"insert\":\"You would actually write out separate IF statements and \\\"add\\\" them together with the delimiter.\\n\\n=IF(Response@row = 1, \\\"Response, \\\") + IF(Reoccurring@row = 1, \\\"Reoccurring, \\\") + so on and so forth...\\n\"}]","format":"rich","dateInserted":"2022-08-30T13:21:31+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341694#Comment_341694","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/341694#Comment_341694

    @Paul Newcome, thank you! Another question I have is, if only 1 box is checked, is it possible to drop the delimiter?

    It also shows a 0 when no boxes are checked, or when technician/set-up/equipment is selected alone.

    image.png
    image.png
    image.png
    image.png


    But seems to be fine when all are selected

    image.png


    I'm using:

    =IF(Response@row = 1, "Response, ") + IF(Reoccurring@row = 1, "Reoccurring, ") + IF(Technician@row = 1, "Technician, ") + IF([Set-Up]@row = 1, "Set-Up, ") + IF(Equipment@row = 1, "Equipment")

    · Share on Facebook Share on Twitter
  • Jimrny
    Jimrny ✭✭

    =IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")

    Fixed my "0" issue, just trying to figure out the best way to have the delimiter drop if there's only one box checked. Any thoughts on this@Paul Newcome?

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this one:

    IF the COUNTIFS of checkboxes is 1 then SUBSTITUTE the comma with a blank, otherwise run the full string.


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1,SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""))

    10xViz.com

    · Share on Facebook Share on Twitter
  • Jimrny
    Jimrny ✭✭
    Try this one:<\/p>

    IF the COUNTIFS of checkboxes is 1 then SUBSTITUTE the comma with a blank, otherwise run the full string.<\/p>

    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(<\/strong>IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", "")<\/strong>, IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""))<\/p>","bodyRaw":"[{\"insert\":\"Try this one:\\nIF the COUNTIFS of checkboxes is 1 then SUBSTITUTE the comma with a blank, otherwise run the full string.\\n\\n=IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, \"},{\"attributes\":{\"bold\":true},\"insert\":\"SUBSTITUTE(\"},{\"insert\":\"IF(Response@row = 1, \\\"Response, \\\", \\\"\\\") + IF(Reoccurring@row = 1, \\\"Reoccurring, \\\", \\\"\\\") + IF(Technician@row = 1, \\\"Technician, \\\", \\\"\\\") + IF([Set-Up]@row = 1, \\\"Set-Up, \\\", \\\"\\\") + IF(Equipment@row = 1, \\\"Equipment\\\", \\\"\\\")\"},{\"attributes\":{\"bold\":true},\"insert\":\", \\\",\\\", \\\"\\\")\"},{\"insert\":\", IF(Response@row = 1, \\\"Response, \\\", \\\"\\\") + IF(Reoccurring@row = 1, \\\"Reoccurring, \\\", \\\"\\\") + IF(Technician@row = 1, \\\"Technician, \\\", \\\"\\\") + IF([Set-Up]@row = 1, \\\"Set-Up, \\\", \\\"\\\") + IF(Equipment@row = 1, \\\"Equipment\\\", \\\"\\\"))\\n\"}]","format":"rich","dateInserted":"2022-08-30T17:23:20+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341749#Comment_341749","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/341749#Comment_341749

    @Paul NewcomeThis worked out perfectly! Any chance you could answer this question about adding a pre-fix if the check boxes are true or not?


    =IF(ISBLANK(Response@row:Equipment@row), "", "Mechanical - " + IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")))

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @JimrnyI'm not sure I follow.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Jimrny
    Jimrny ✭✭
    edited 08/31/22
    @Jimrny<\/a> I'm not sure I follow.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" I'm not sure I follow.\\n\"}]","format":"rich","dateInserted":"2022-08-31T13:20:10+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341868#Comment_341868","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/341868#Comment_341868

    @Paul NewcomeSorry I mean, I was trying to display the text "MECHANICAL" in front of the joined values.

    MECHANICAL -Response, Reoccurring, Equipment

    I tried adding ="Mechanical" + String, but it leaves the value there if there are no check boxes selected.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Give this a go...


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) > 0, "MECHANICAL - " +IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")))

    10xViz.com

    · Share on Facebook Share on Twitter
  • Jimrny
    Jimrny ✭✭
    Give this a go...<\/p>

    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) > 0, "MECHANICAL - " + <\/strong>IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")))<\/strong><\/p>","bodyRaw":"[{\"insert\":\"Give this a go...\\n\\n=\"},{\"attributes\":{\"bold\":true},\"insert\":\"IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) > 0, \\\"MECHANICAL - \\\" + \"},{\"insert\":\"IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, \\\"Response, \\\", \\\"\\\") + IF(Reoccurring@row = 1, \\\"Reoccurring, \\\", \\\"\\\") + IF(Technician@row = 1, \\\"Technician, \\\", \\\"\\\") + IF([Set-Up]@row = 1, \\\"Set-Up, \\\", \\\"\\\") + IF(Equipment@row = 1, \\\"Equipment\\\", \\\"\\\"), \\\",\\\", \\\"\\\"), IF(Response@row = 1, \\\"Response, \\\", \\\"\\\") + IF(Reoccurring@row = 1, \\\"Reoccurring, \\\", \\\"\\\") + IF(Technician@row = 1, \\\"Technician, \\\", \\\"\\\") + IF([Set-Up]@row = 1, \\\"Set-Up, \\\", \\\"\\\") + IF(Equipment@row = 1, \\\"Equipment\\\", \\\"\\\"))\"},{\"attributes\":{\"bold\":true},\"insert\":\")\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-08-31T16:10:23+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341899#Comment_341899","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/341899#Comment_341899

    @Paul NewcomeThat makes sense to count the range with true values being greater than 0. Thank you so much!!

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    10xViz.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Help shape the future of Smartsheet.
分享你的想法和特性请求。

 Watch video
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/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":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/94808/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/94808/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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"}]}],"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

  • HAS exact match within multiselect - Numbered Values

    场景:如果试图确定一个匹配a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…

  • I am having trouble using "And", "OR" & "Countif(s)" to build a formula.

    Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…

  • Help with date calculation formula

    Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…