Formulas and Functions

0, FIND(\"exist\", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(WEEKNUMBER(@cell), [1\/7\/19]$1) <= [1\/7\/19]$1, {Implementation Rollup Work Finish}, IFERROR(WEEKNUMBER(@cell), [1\/7\/19]$1) >= [1\/7\/19]$1, {Implementation…","categoryID":322,"dateInserted":"2019-04-09T11:53:33+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2020-10-22T16:48:16+00:00","insertUserID":45516,"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-07-17T15:25:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":124108,"lastUser":{"userID":124108,"name":"Cat Werbeck","url":"https:\/\/community.smartsheet.com\/profile\/Cat%20Werbeck","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zAjcs-xjxyY!7rFcOyWHXPo!XvnrJU9jDp7","dateLastActive":"2020-12-23T20:01:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":275,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/45446\/formula-woes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/45446\/formula-woes","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":72481,"type":"question","name":"Checkbox formula with percentage","excerpt":"Smartsheet guru's-- I'm still learning how to use all of Smartsheet's assets. I am trying to show a percentage for checked boxes. Can someone help me with the formula part? Thank you!","categoryID":322,"dateInserted":"2020-10-22T15:02:48+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T16:15:53+00:00","insertUserID":127896,"insertUser":{"userID":127896,"name":"Tara Knox","url":"https:\/\/community.smartsheet.com\/profile\/Tara%20Knox","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nW1W8KHNFR845.png","dateLastActive":"2020-12-18T12:46:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":1813,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/72481\/checkbox-formula-with-percentage","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72481\/checkbox-formula-with-percentage","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q2WBJ9I6RFHQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T16:18:07+00:00","dateAnswered":"2020-10-22T15:07:20+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":72457,"type":"question","name":"Formula to create a number assignment catalog","excerpt":"Hello, I am trying to create a number assignment catalog that is automated to speak to a smartsheet that has my full collection of products. The goal is when creating a new product anyone could fill out a form select the category numbers that will define the department and then select a a number that will identify the…","categoryID":322,"dateInserted":"2020-10-21T21:36:48+00:00","dateUpdated":"2020-10-21T21:38:06+00:00","dateLastComment":"2020-10-22T15:16:47+00:00","insertUserID":127490,"insertUser":{"userID":127490,"name":"Aaron Olsen","url":"https:\/\/community.smartsheet.com\/profile\/Aaron%20Olsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-24T20:21:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":127490,"lastUserID":127490,"lastUser":{"userID":127490,"name":"Aaron Olsen","url":"https:\/\/community.smartsheet.com\/profile\/Aaron%20Olsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-24T20:21:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":157,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/72457\/formula-to-create-a-number-assignment-catalog","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72457\/formula-to-create-a-number-assignment-catalog","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T15:15:44+00:00","dateAnswered":"2020-10-21T23:50:52+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":72473,"type":"question","name":"Pulling data from a sheet","excerpt":"Good Morning, I have a report that I do monthly that records the data from the following sheet. At the end of the month I usually filter columns and manually count errors for each responsible party and move them to a another sheet that records each persons errors for the whole year. I record data for about 30 individuals…","categoryID":322,"dateInserted":"2020-10-22T11:59:01+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T14:01:38+00:00","insertUserID":127701,"insertUser":{"userID":127701,"name":"Austin Bell","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Austin%20Bell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-02-01T12:24:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":1145,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/72473\/pulling-data-from-a-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72473\/pulling-data-from-a-sheet","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N3AZYF6ZOH18\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T14:00:42+00:00","dateAnswered":"2020-10-22T12:53:46+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":72469,"type":"question","name":"sequential number for each parent row - column formula","excerpt":"Hello im trying to generate a sequential number for each parent row. I can do this using an helper column called parent that flags each parent record with 1 then the formula @row, 1) this won't convert to a column formula because of the explicit row references used to define the range. can it be done. any help appreciated…","categoryID":322,"dateInserted":"2020-10-22T10:08:44+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T13:35:29+00:00","insertUserID":127885,"insertUser":{"userID":127885,"name":"DHU - PMO","url":"https:\/\/community.smartsheet.com\/profile\/DHU%20-%20PMO","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-12T06:22:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":133,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72469\/sequential-number-for-each-parent-row-column-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72469\/sequential-number-for-each-parent-row-column-formula","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":72462,"type":"question","name":"Accounting for the word \"Perpetual\" in a date field when there is no end date","excerpt":"I am currently running this formula: =[Contract Expiration Date]@row - [Notify days to renew\/cancel]@row to calculate the date when we need to review a contract. It essentially takes the end date of the contract and subtracts the numbers of days specified in the contract if you want to cancel. We have some contracts with…","categoryID":322,"dateInserted":"2020-10-22T01:10:19+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T13:34:19+00:00","insertUserID":127695,"insertUser":{"userID":127695,"name":"Emily McNeeley","url":"https:\/\/community.smartsheet.com\/profile\/Emily%20McNeeley","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!26jRP0ygZBY!wulddzL-Obw!pJP7L_r-ZLW","dateLastActive":"2022-10-23T10:29:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":88,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72462\/accounting-for-the-word-perpetual-in-a-date-field-when-there-is-no-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72462\/accounting-for-the-word-perpetual-in-a-date-field-when-there-is-no-end-date","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T13:29:27+00:00","dateAnswered":"2020-10-22T13:03:07+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":72333,"type":"question","name":"Column Formula not selectable as Gantt Label","excerpt":"Hi Folks, Is this a bug? For Gantt chart labels, I use a column that concatenates and formats the task and end dates; call this column \"Gantt Label\". I select this as my Gantt label and it looks great. To prevent my formula from getting corrupted or broken, I convert \"Gantt Label\" to a Column Formula. Here's where it gets…","categoryID":322,"dateInserted":"2020-10-17T00:40:44+00:00","dateUpdated":null,"dateLastComment":"2020-10-21T21:39:02+00:00","insertUserID":3351,"insertUser":{"userID":3351,"name":"Jimmy Poon","url":"https:\/\/community.smartsheet.com\/profile\/Jimmy%20Poon","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAdDoda45zw!pEjAqe7WqSQ!b0sos148Vrg","dateLastActive":"2020-11-10T21:28:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":10959,"lastUser":{"userID":10959,"name":"Ben Goldblatt","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20Goldblatt","photoUrl":"https:\/\/lh3.googleusercontent.com\/-7T1VCkawxAM\/AAAAAAAAAAI\/AAAAAAAAABI\/aGii4NgwZC0\/photo.jpg","dateLastActive":"2023-04-18T18:10:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":183,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72333\/column-formula-not-selectable-as-gantt-label","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72333\/column-formula-not-selectable-as-gantt-label","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-21T21:46:17+00:00","dateAnswered":"2020-10-21T21:39:02+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources