Formulas and Functions

=TODAY()) This worked for over a year but suddenly is giving an error message, any idea if something in the TODAY function changed?","snippet":"In a SUMIFS function I am using a date check to sum all cells that have an expiry date equal or greater than TODAY: =SUMIFS(Expiry:Expiry, >=TODAY()) This worked for over a year…","categoryID":322,"dateInserted":"2023-10-19T19:55:02+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T20:48:29+00:00","insertUserID":159456,"insertUser":{"userID":159456,"name":"jkt2001","url":"https:\/\/community.smartsheet.com\/profile\/jkt2001","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:45:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165153,"lastUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:46:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":34,"score":null,"hot":3395497411,"url":"https:\/\/community.smartsheet.com\/discussion\/111927\/today-function-randomly-stopped-working-in-my-summary-metric-formulas-how-do-i-fix","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111927\/today-function-randomly-stopped-working-in-my-summary-metric-formulas-how-do-i-fix","format":"Rich","lastPost":{"discussionID":111927,"commentID":400855,"name":"Re: TODAY Function randomly stopped working in my Summary Metric formulas, how do I fix?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400855#Comment_400855","dateInserted":"2023-10-19T20:48:29+00:00","insertUserID":165153,"insertUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:46: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-19T20:46:41+00:00","dateAnswered":"2023-10-19T20:38:18+00:00","acceptedAnswers":[{"commentID":400853,"body":"

@jkt2001,<\/p>

I would check all the ranges being used as criteria and ensure all the data is in the format expected.<\/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":111907,"type":"question","name":"How do you calculate a date based on text in another cell?","excerpt":"I need to Smartsheet to use the name of a company in another cell to determine which formula to use to calculate a date in another cell. I am trying to calculate the conversion date of a temp, and based on which agency we are using the fee structure is different. Some may convert at 120 days and others at 180 days. If…","snippet":"I need to Smartsheet to use the name of a company in another cell to determine which formula to use to calculate a date in another cell. I am trying to calculate the conversion…","categoryID":322,"dateInserted":"2023-10-19T15:10:51+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T16:08:07+00:00","insertUserID":168790,"insertUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168790,"lastUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3395461138,"url":"https:\/\/community.smartsheet.com\/discussion\/111907\/how-do-you-calculate-a-date-based-on-text-in-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111907\/how-do-you-calculate-a-date-based-on-text-in-another-cell","format":"Rich","lastPost":{"discussionID":111907,"commentID":400786,"name":"Re: How do you calculate a date based on text in another cell?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400786#Comment_400786","dateInserted":"2023-10-19T16:08:07+00:00","insertUserID":168790,"insertUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+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-10-19T19:20:18+00:00","dateAnswered":"2023-10-19T15:27:09+00:00","acceptedAnswers":[{"commentID":400776,"body":"

@LMA<\/a> <\/p>

Something like the below should work for you<\/p>

=IF(Agency@row=\"Agency 1\", [Start Date]@row +120,IF(Agency@row=\"Agency 2\", [Start Date]@row +180))<\/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":111904,"type":"question","name":"Unparseable Error: Check \"Checkbox\" when conditions are met","excerpt":"Hello. I have a helper checkbox column. I would like a formula to \"check the box\" when the content in the column \"Frequency L4\" is not blank. In addition, I would like any ancestor row of that \"not blank\" row marked with the check box as well. So far I have only worked on the first part and get errors. Any ideas on how to…","snippet":"Hello. I have a helper checkbox column. I would like a formula to \"check the box\" when the content in the column \"Frequency L4\" is not blank. In addition, I would like any…","categoryID":322,"dateInserted":"2023-10-19T14:05:52+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T14:23:11+00:00","insertUserID":168777,"insertUser":{"userID":168777,"name":"Terri1019","url":"https:\/\/community.smartsheet.com\/profile\/Terri1019","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZLnq2Q_pbDI!bDNrGweNSvI!C6YbfY8g90R","dateLastActive":"2023-10-19T14:20:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168777,"lastUser":{"userID":168777,"name":"Terri1019","url":"https:\/\/community.smartsheet.com\/profile\/Terri1019","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZLnq2Q_pbDI!bDNrGweNSvI!C6YbfY8g90R","dateLastActive":"2023-10-19T14:20:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3395450943,"url":"https:\/\/community.smartsheet.com\/discussion\/111904\/unparseable-error-check-checkbox-when-conditions-are-met","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111904\/unparseable-error-check-checkbox-when-conditions-are-met","format":"Rich","lastPost":{"discussionID":111904,"commentID":400757,"name":"Re: Unparseable Error: Check \"Checkbox\" when conditions are met","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400757#Comment_400757","dateInserted":"2023-10-19T14:23:11+00:00","insertUserID":168777,"insertUser":{"userID":168777,"name":"Terri1019","url":"https:\/\/community.smartsheet.com\/profile\/Terri1019","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZLnq2Q_pbDI!bDNrGweNSvI!C6YbfY8g90R","dateLastActive":"2023-10-19T14:20:36+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-10-19T14:22:20+00:00","dateAnswered":"2023-10-19T14:14:50+00:00","acceptedAnswers":[{"commentID":400755,"body":"

Hi Terri,<\/p>

The #UNPARSABLE error<\/a> is thrown when \"the formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.\"<\/p>

ISBLANK<\/a> is a formula of it's own. It is expecting an input value between parenthesis. Here is an updated version of your formula<\/p>

=IF(NOT(ISBLANK([Frequency L4]@row)), 1, 0)<\/p>

Here is a help article<\/a> which should help with your other question regarding parent\/children rows.<\/p>

Hope this helps!<\/p>

Sal<\/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":[]}">