Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook templateto view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
❓️Product questions
Sign in andask it here!The community's got your back.

Wondering if there’s a problem?
Check out theStatus Pageand subscribe for notifications.

Check what Support you have access to based on your Plan
Overview of Smartsheet support hours and resources

Share your knowledge!
Help others byanswering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

0, [Estimated Hours:]4, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\") which works great, but add in other words such as Graphics to it. So…","categoryID":322,"dateInserted":"2019-04-30T14:00:47+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-02T08:23:03+00:00","insertUserID":66016,"insertUser":{"userID":66016,"name":"BTMarketingOps","url":"https:\/\/community.smartsheet.com\/profile\/BTMarketingOps","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-18T19:39:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":1668,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/47011\/if-find-function-with-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47011\/if-find-function-with-or","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":47021,"type":"discussion","name":"Finding and returning values in a range of cells","excerpt":"Hi all, I'm having a lot of trouble joining cells that contain a certain value (because there is no contain function). I have a range of cells that are all in a single row, and I'm trying to collect all of the cells that contain a certain value, and join them together. For example, say I had a row with 4 columns that say…","categoryID":322,"dateInserted":"2019-04-30T14:19:03+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-02T08:17:59+00:00","insertUserID":72196,"insertUser":{"userID":72196,"name":"erober01","url":"https:\/\/community.smartsheet.com\/profile\/erober01","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T14:12:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":1631,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/47021\/finding-and-returning-values-in-a-range-of-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47021\/finding-and-returning-values-in-a-range-of-cells","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"},{"tagID":324,"urlcode":"community-platform","name":"Community Platform"},{"tagID":446,"urlcode":"update-requests","name":"Update Requests"}],"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":41076,"type":"discussion","name":"Pivot Sheet & formulas","excerpt":"HI, I've used pivot sheet to gather required information to show up in a sheet. I've attached a clip of the final result which works great except for the fact that I have formulas in some cells. When a new row is created, the formulas in the cells from above don't copy into the new cells. Can someone help me out. thanks!","categoryID":322,"dateInserted":"2019-02-08T11:56:26+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-02T04:01:41+00:00","insertUserID":44251,"insertUser":{"userID":44251,"name":"Frankie_2018","url":"https:\/\/community.smartsheet.com\/profile\/Frankie_2018","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-02-08T11:41:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":32601,"lastUser":{"userID":32601,"name":"haznedarj","url":"https:\/\/community.smartsheet.com\/profile\/haznedarj","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-19T23:37:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":111,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/41076\/pivot-sheet-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/41076\/pivot-sheet-formulas","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-02\/63\/90\/6390f6df056eb0be8346cd274127fb38.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"PivotSheet.JPG"},"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"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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":47171,"type":"discussion","name":"Auto change a cell based on a checkbox in a different cell (all in the same row though) or change in","excerpt":"I created a pretty intense sheet to keep track on invoices and receipts. I have an \"Action\" column that is a drop down column. Here are a few of those \"Actions\" Request Sponsorship Submit Sponsorship Request Invoice Call to pay with CC Call back to pay Submit Check Request Follow-Up Check Approval Mail Check Out Request…","categoryID":322,"dateInserted":"2019-05-01T17:31:15+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-02T03:45:48+00:00","insertUserID":87161,"insertUser":{"userID":87161,"name":"Diana.Martinez.Intern","url":"https:\/\/community.smartsheet.com\/profile\/Diana.Martinez.Intern","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-05-06T16:31:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-07-17T10:15:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":231,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/47171\/auto-change-a-cell-based-on-a-checkbox-in-a-different-cell-all-in-the-same-row-though-or-change-in","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47171\/auto-change-a-cell-based-on-a-checkbox-in-a-different-cell-all-in-the-same-row-though-or-change-in","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":208,"urlcode":"Workspaces","name":"Workspaces"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":277,"urlcode":"Integrations","name":"Integrations"},{"tagID":283,"urlcode":"Enhancement Request","name":"Enhancement Request"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"},{"tagID":300,"urlcode":"Email Notifications","name":"Email Notifications"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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":47096,"type":"discussion","name":"Attachments","excerpt":"Is there a way to deny being able to check a box complete until an attachment is added. Thanks in advance","categoryID":322,"dateInserted":"2019-05-01T10:03:11+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-02T03:16:30+00:00","insertUserID":87081,"insertUser":{"userID":87081,"name":"Dustin.Bissette","url":"https:\/\/community.smartsheet.com\/profile\/Dustin.Bissette","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-07-19T11:24:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-07-17T10:15:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":52,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/47096\/attachments","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47096\/attachments","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":218,"urlcode":"Construction","name":"Construction"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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":46926,"type":"discussion","name":"Vlookup or Index with Match?","excerpt":"Trying to get data from one sheet to another by matching on Deal ID to the Requests sheet from the Salesforce sheet Could someone please help me for the formula for this? this would be an import from salesforce that I would have to do manually weekly, so I could rename the imported file and use same column names each time.…","categoryID":322,"dateInserted":"2019-04-29T17:08:09+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T21:19:45+00:00","insertUserID":2469,"insertUser":{"userID":2469,"name":"Lisa Bruce","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Bruce","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BdlAgXbW9Tw!u4jC1w71orQ!HxipPbTNXr2","dateLastActive":"2022-09-15T22:38:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":2469,"lastUser":{"userID":2469,"name":"Lisa Bruce","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Bruce","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BdlAgXbW9Tw!u4jC1w71orQ!HxipPbTNXr2","dateLastActive":"2022-09-15T22:38:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":842,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/46926\/vlookup-or-index-with-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46926\/vlookup-or-index-with-match","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-04\/db\/db\/dbdb054fc71df439662b62eff46a0310.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"help requests.png"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":207,"urlcode":"Sales","name":"Sales"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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":11536,"type":"discussion","name":"Identifying a ticked checkbox in an =IF formula","excerpt":"Hi there, I'd like to use an =IF formula to identify those cells where the checkbox is ticked - does anyone know what syntax should be used to identify a ticked checkbox, i.e. =IF(cell = ????,1,0) (where ???? = ticked checkbox)? Thanks, Paul","categoryID":322,"dateInserted":"2017-07-19T09:07:55+00:00","dateUpdated":"2022-10-27T16:44:06+00:00","dateLastComment":"2019-05-01T19:48:58+00:00","insertUserID":10125,"insertUser":{"userID":10125,"name":"Paul R","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20R","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-08-18T23:34:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-07-13T20:11:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":19918,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/11536\/identifying-a-ticked-checkbox-in-an-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/11536\/identifying-a-ticked-checkbox-in-an-if-formula","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":211,"urlcode":"Manufacturing","name":"Manufacturing"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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":47166,"type":"discussion","name":"Auto fill due date based on my auto filled start date","excerpt":"I have my start date auto filled when I enter information into a new row. I now want my due date to auto fill to 7 work days after the start date. How do I do this? Right now I have this for row 5 =WORKDAY([Start Date]5 + 7) Any help would be great!","categoryID":322,"dateInserted":"2019-05-01T17:06:13+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T17:13:52+00:00","insertUserID":87161,"insertUser":{"userID":87161,"name":"Diana.Martinez.Intern","url":"https:\/\/community.smartsheet.com\/profile\/Diana.Martinez.Intern","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-05-06T16:31:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-07-13T20:11:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":257,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/47166\/auto-fill-due-date-based-on-my-auto-filled-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47166\/auto-fill-due-date-based-on-my-auto-filled-start-date","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":45916,"type":"discussion","name":"IF todays date is between start date and end date then check this box","excerpt":"I need to run an active projects report that collects data on in progress projects. I need to put the formula into the checklist column so that it checks it if todays date is between the start and end date.","categoryID":322,"dateInserted":"2019-04-15T18:46:35+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T16:16:48+00:00","insertUserID":85511,"insertUser":{"userID":85511,"name":"kjsneaks","url":"https:\/\/community.smartsheet.com\/profile\/kjsneaks","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-05-08T11:43:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":738,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/45916\/if-todays-date-is-between-start-date-and-end-date-then-check-this-box","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/45916\/if-todays-date-is-between-start-date-and-end-date-then-check-this-box","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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hey @decrom<\/a> <\/p>

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?<\/p>

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\"), \"Untrained\", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\")), \"In Progress\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"In Process\") >= 1, \"In Process\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Trained\") >= 1, \"Trained\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Proficient\") >= 1, \"Proficient\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Preceptor\") >= 1, \"Preceptor\")))))))<\/p>

Please let me know what we need to tweak<\/p>

Kelly<\/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":107613,"type":"question","name":"Add pictures inside a cells which look like a form - Workapps","excerpt":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt look likes a grid view but like card and we can add pictures inside a cells. You can see that in the picture that I add. If anydbody have a…","snippet":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt…","categoryID":322,"dateInserted":"2023-07-14T10:56:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T10:18:58+00:00","insertUserID":151421,"insertUser":{"userID":151421,"name":"lea.s6","url":"https:\/\/community.smartsheet.com\/profile\/lea.s6","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G69Wn8Gn53s!xLDXMWmx7Oc!BP_1d2Auq2F","dateLastActive":"2023-07-17T10:14:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151421,"lastUser":{"userID":151421,"name":"lea.s6","url":"https:\/\/community.smartsheet.com\/profile\/lea.s6","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G69Wn8Gn53s!xLDXMWmx7Oc!BP_1d2Auq2F","dateLastActive":"2023-07-17T10:14:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":42,"score":null,"hot":3378924338,"url":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","format":"Rich","lastPost":{"discussionID":107613,"commentID":385520,"name":"Re: Add pictures inside a cells which look like a form - Workapps","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385520#Comment_385520","dateInserted":"2023-07-17T10:18:58+00:00","insertUserID":151421,"insertUser":{"userID":151421,"name":"lea.s6","url":"https:\/\/community.smartsheet.com\/profile\/lea.s6","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G69Wn8Gn53s!xLDXMWmx7Oc!BP_1d2Auq2F","dateLastActive":"2023-07-17T10:14:44+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ZA10DMWH9B2G\/image-2023-07-14-11-46-59.jpeg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Image 2023-07-14 11-46-59.jpeg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T10:19:13+00:00","dateAnswered":"2023-07-17T10:08:37+00:00","acceptedAnswers":[{"commentID":385518,"body":"

@lea.s6<\/a> when a page is loaded in WorkApps the setting related to how the page shows on a mobile can be amended here:<\/p>

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


<\/p>"},{"commentID":385520,"body":"

Thanks for your answer !<\/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":107647,"type":"question","name":"Formula help","excerpt":"Can someone help me with this formula. Right now it works correctly only when a date is in the warranty start column. However when there is no date in the warranty start date column is says invalid data type. So I think I am missing some command with \"//www.santa-greenland.com/community/categories/formulas-and-functions/\" but now sure where or how it should be written. Thanks","snippet":"Can someone help me with this formula. Right now it works correctly only when a date is in the warranty start column. However when there is no date in the warranty start date…","categoryID":322,"dateInserted":"2023-07-15T14:38:50+00:00","dateUpdated":null,"dateLastComment":"2023-07-15T15:42:03+00:00","insertUserID":144391,"insertUser":{"userID":144391,"name":"Jon Barto","url":"https:\/\/community.smartsheet.com\/profile\/Jon%20Barto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-15T15:37:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":66106,"lastUser":{"userID":66106,"name":"Peggy Parchert","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Peggy%20Parchert","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YYRDMWWFX83G\/nC7HLGW82ACPT.jpg","dateLastActive":"2023-07-15T15:41:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":51,"score":null,"hot":3378871853,"url":"https:\/\/community.smartsheet.com\/discussion\/107647\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107647\/formula-help","format":"Rich","lastPost":{"discussionID":107647,"commentID":385476,"name":"Re: Formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385476#Comment_385476","dateInserted":"2023-07-15T15:42:03+00:00","insertUserID":66106,"insertUser":{"userID":66106,"name":"Peggy Parchert","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Peggy%20Parchert","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YYRDMWWFX83G\/nC7HLGW82ACPT.jpg","dateLastActive":"2023-07-15T15:41:25+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8EEBFF0ESKU2\/date-formula.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"date formula.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T08:26:54+00:00","dateAnswered":"2023-07-15T15:37:30+00:00","acceptedAnswers":[{"commentID":385474,"body":"

@Jon Barto<\/a> <\/p>

Try<\/p>

=IFERROR(DATE(YEAR([warranty Start Date]@row) + 2, MONTH([warranty Start Date]@row), DAY([warranty Start Date]@row)), \"No Date\")<\/p>

Peggy<\/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