Formulas and Functions

= [CHECK-IN]@row, [TUESDAY 4\/20]17 <= [CHECK-OUT]@row), \"X\") Check In and…","categoryID":322,"dateInserted":"2021-03-28T18:04:53+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T13:57:21+00:00","insertUserID":121079,"insertUser":{"userID":121079,"name":"Jen Evans","url":"https:\/\/community.smartsheet.com\/profile\/Jen%20Evans","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-29T13:41:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-27T07:46:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":120,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77759\/if-and-formula-with-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77759\/if-and-formula-with-dates","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/5CH3Y7VRAKCK\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-29T11:33:56+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":77735,"type":"question","name":"Is there a way to simplify this formula?","excerpt":"I have 3 columns with dropdowns of 'Complete' 'In progress' and 'Not Started'. In a fourth column (Percentage of Task Complete) I have a formula that assigns a value to each of those text items and then does an average and converts it to a percentage. Here is the formula: =(SUM(IF([Step 1]@row = \"complete\", 100, IF([Step…","categoryID":322,"dateInserted":"2021-03-26T18:09:26+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T11:37:27+00:00","insertUserID":134187,"insertUser":{"userID":134187,"name":"Taylor Scott","url":"https:\/\/community.smartsheet.com\/profile\/Taylor%20Scott","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HYjS30zvkxY!xplQsfDX_bw!j9b3_pu7Fcp","dateLastActive":"2023-08-23T17:08:55+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":73,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77735\/is-there-a-way-to-simplify-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77735\/is-there-a-way-to-simplify-this-formula","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/TFEIXY8D8V50\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-27T21:03:16+00:00","dateAnswered":"2021-03-26T19:08:23+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":77691,"type":"question","name":"Smartsheet Copy Rows Automation is Duplicating Data","excerpt":"Hi All, I have a Smartsheet set up which includes an automation to copy the rows to a second Smartsheet which works as a Pro Forma sheet for our Accounts Department. The idea is that we will input data into Cells as normal and then once our Client has approved the proposal, they will 'Approve' in Smartsheet via a dropdown.…","categoryID":322,"dateInserted":"2021-03-25T16:57:39+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T10:48:43+00:00","insertUserID":93696,"insertUser":{"userID":93696,"name":"Matt Wood","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Wood","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!oSFZ6LXdk_o!DWHYgx3iyn4!s2JrvKNOHIc","dateLastActive":"2023-01-03T14:13:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":93696,"lastUser":{"userID":93696,"name":"Matt Wood","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Wood","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!oSFZ6LXdk_o!DWHYgx3iyn4!s2JrvKNOHIc","dateLastActive":"2023-01-03T14:13:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":140,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/77691\/smartsheet-copy-rows-automation-is-duplicating-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77691\/smartsheet-copy-rows-automation-is-duplicating-data","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JAA9M90PCZMO\/screenshot-2021-03-25-165636.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2021-03-25 165636.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-26T08:21:03+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":77719,"type":"question","name":"need to find values","excerpt":"here i have the data with few country names with status as pass , fail and on hold. attaching screenshots below. i need to figure out the values with the help of country names EX; AMERICAS = Pass (no of counts), Failed (no of counts) India = Pass (no of counts),Failed (no of counts) plz suggest me how to achieve this.","categoryID":322,"dateInserted":"2021-03-26T07:12:38+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T10:04:45+00:00","insertUserID":131631,"insertUser":{"userID":131631,"name":"Rakesh Lavishetty","url":"https:\/\/community.smartsheet.com\/profile\/Rakesh%20Lavishetty","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-30T03:15:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":131631,"lastUser":{"userID":131631,"name":"Rakesh Lavishetty","url":"https:\/\/community.smartsheet.com\/profile\/Rakesh%20Lavishetty","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-30T03:15:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":64,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/77719\/need-to-find-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77719\/need-to-find-values","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-29T10:05:12+00:00","dateAnswered":"2021-03-26T07:34:24+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":77681,"type":"question","name":"Per diem interest rounded to nearest cent doesn't translate to accumulated interest?","excerpt":"Happy Thursday!!! I've got a sheet that tracks loans to generate statements and payoffs. For the most part, it works GREAT. The \"Holding Cost\" (per diem) column is run by =[Loan amount]@row * interest rate \/ 365 Since the column is set for Text\/Number & Currency, it automatically rounds to the nearest cent which is perfect…","categoryID":322,"dateInserted":"2021-03-25T14:22:24+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T08:43:30+00:00","insertUserID":131863,"insertUser":{"userID":131863,"name":"Jennifer Sizelove","url":"https:\/\/community.smartsheet.com\/profile\/Jennifer%20Sizelove","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QQMZE6PW5I3T\/nDJ6NZ8B5W8XJ.JPG","dateLastActive":"2021-04-14T18:48:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-27T07:46:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":64,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77681\/per-diem-interest-rounded-to-nearest-cent-doesnt-translate-to-accumulated-interest","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77681\/per-diem-interest-rounded-to-nearest-cent-doesnt-translate-to-accumulated-interest","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RKN46ALYI349\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-26T18:59:05+00:00","dateAnswered":"2021-03-26T12:16:23+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":77768,"type":"question","name":"Logic","excerpt":"Hi, I have created a form for leave request for my office. I would like to add a logic (or any other way) that \"if the start date and end date are same\" than a new field with the option to select a \"full day leave or half day leave\" appears. If suggest if its possible or any other alternative to do so. thanks in advance.","categoryID":322,"dateInserted":"2021-03-29T04:41:07+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T07:13:34+00:00","insertUserID":131837,"insertUser":{"userID":131837,"name":"Anoop Phago","url":"https:\/\/community.smartsheet.com\/profile\/Anoop%20Phago","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xQjQsWzh7qY!wilR_zTZu7o!-0E4OLwESuh","dateLastActive":"2023-06-23T03:51:47+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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":51,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/77768\/logic","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77768\/logic","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-29T07:02:04+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":77764,"type":"question","name":"Auto filtering to another sheet","excerpt":"I have a grid sheet with a form for a training sign off(UTV). I'd like to take the date \"Date\" and name \"Name\" of the person who filled out the form and move\/copy just these two columns to a new sheet. The new sheet has columns that are different than the previous sheet, in that the date I moved from the UTV sign off…","categoryID":322,"dateInserted":"2021-03-28T23:51:48+00:00","dateUpdated":null,"dateLastComment":"2021-03-29T06:59:53+00:00","insertUserID":134227,"insertUser":{"userID":134227,"name":"Rachel Rudd","url":"https:\/\/community.smartsheet.com\/profile\/Rachel%20Rudd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-03-01T17:45:06+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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":666,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/77764\/auto-filtering-to-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77764\/auto-filtering-to-another-sheet","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/LLA67UCQCL0U\/sm1-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SM1.JPG"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-29T06:59:53+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":77769,"type":"question","name":"Minimizing Data in Email","excerpt":"Hello, I have created a leave request form for my organization. I have created a Automation in the leave request form. But when someone submit the form their respective supervisor and HR gets the notification with all the fields as per the main sheet. I would like to minimize it and only send the relevant data for the…","categoryID":322,"dateInserted":"2021-03-29T05:14:21+00:00","dateUpdated":"2021-03-29T05:16:06+00:00","dateLastComment":"2021-03-29T06:54:14+00:00","insertUserID":134232,"insertUser":{"userID":134232,"name":"Anoop Limbu","url":"https:\/\/community.smartsheet.com\/profile\/Anoop%20Limbu","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-29T05:08:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":134232,"lastUserID":127340,"lastUser":{"userID":127340,"name":"Bassam Khalil","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Bassam%20Khalil","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf7UK_uIEyXZAONqmsQXBPSfVi5-GCzNLZawKE6bV9IIEk=s96-c","dateLastActive":"2023-07-18T09:22:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":53,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/77769\/minimizing-data-in-email","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77769\/minimizing-data-in-email","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/NGF3EKG7QMDT\/notification2-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"notification2.JPG"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-29T06:49:27+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