Formulas and Functions

=MAX(COLLECT({Date Column}, {User Column}, @cell = userName@row))<\/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":108991,"type":"question","name":"RYG status set by % Complete and End Date","excerpt":"I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please help!!! Here's what I'm trying to accomplish: Green = If % complete is 100% or if end date is greater than today Yellow = If % complete is not…","snippet":"I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please…","categoryID":322,"dateInserted":"2023-08-15T18:30:55+00:00","dateUpdated":null,"dateLastComment":"2023-08-16T12:38:00+00:00","insertUserID":165137,"insertUser":{"userID":165137,"name":"dhoch","url":"https:\/\/community.smartsheet.com\/profile\/dhoch","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!07FQv_TRRH0!wVFS8KDr0Xs!FO5J7dYBxHv","dateLastActive":"2023-08-16T13:56:29+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-16T13:54:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":73,"score":null,"hot":3384319735,"url":"https:\/\/community.smartsheet.com\/discussion\/108991\/ryg-status-set-by-complete-and-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108991\/ryg-status-set-by-complete-and-end-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108991,"commentID":390848,"name":"Re: RYG status set by % Complete and End Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390848#Comment_390848","dateInserted":"2023-08-16T12:38:00+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-08-16T13:54:01+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\/U2DYEDL6DO7T\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"smartsheet.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-15T19:27:36+00:00","dateAnswered":"2023-08-15T19:22:58+00:00","acceptedAnswers":[{"commentID":390746,"body":"

Try this:<\/p>

=IF(OR([% Complete]@row = 1, [End Date]@row> TODAY(7)), \"Green\", IF([End Date]@row< TODAY(), \"Red\", \"Yellow\"))<\/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":108981,"type":"question","name":"Date Comparison Formula Help","excerpt":"I am a SmartSheet novice, but my team is used to base tracking in SmartSheet. I would like to set up conditional formatting that highlights if a site's planned schedule puts the site at risk. I would like to compare two columns: \"Construction Schedule: Turnover to Retail Date\" against \"Exterior Installation: Scheduled…","snippet":"I am a SmartSheet novice, but my team is used to base tracking in SmartSheet. I would like to set up conditional formatting that highlights if a site's planned schedule puts the…","categoryID":322,"dateInserted":"2023-08-15T16:30:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-16T14:29:44+00:00","insertUserID":165134,"insertUser":{"userID":165134,"name":"Tartaruchus77","title":"Sr Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Tartaruchus77","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-16T14:49:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":159703,"lastUser":{"userID":159703,"name":"Monique_Odom_Comcast","title":"Process Manager","url":"https:\/\/community.smartsheet.com\/profile\/Monique_Odom_Comcast","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zUkrM9vny0o!ZQkomVvUbwo!vBgDVSQJmmG","dateLastActive":"2023-08-16T14:54:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":30,"score":null,"hot":3384313833,"url":"https:\/\/community.smartsheet.com\/discussion\/108981\/date-comparison-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108981\/date-comparison-formula-help","format":"Rich","tagIDs":[254,437],"lastPost":{"discussionID":108981,"commentID":390884,"name":"Re: Date Comparison Formula Help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390884#Comment_390884","dateInserted":"2023-08-16T14:29:44+00:00","insertUserID":159703,"insertUser":{"userID":159703,"name":"Monique_Odom_Comcast","title":"Process Manager","url":"https:\/\/community.smartsheet.com\/profile\/Monique_Odom_Comcast","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zUkrM9vny0o!ZQkomVvUbwo!vBgDVSQJmmG","dateLastActive":"2023-08-16T14:54:15+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-16T14:52:00+00:00","dateAnswered":"2023-08-16T14:29:44+00:00","acceptedAnswers":[{"commentID":390884,"body":"

Hello @Tartaruchus77<\/a>,<\/p>

First, you'll want to create a Checkbox column that is not<\/em> restricted to checkbox use only. Then, use the following formula:<\/p>

=IF([Construction Schedule: Turnover to Retail Date]@row < ([Exterior Installation: Scheduled Date]@row + 14), true)<\/p>

Convert it to a column formula, and then you'll be able to create a Conditional Formatting rule for when that column is checked!<\/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"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]}],"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":[]}">