Actual Date IF Formula for RYG Balls
Hi all!
I would like my RYG balls to express the following (actual dates, not cell reference):
Green = before 21 August 2020
Yellow = after 21 August 2020
Red = after 28 August 2020
This is my current formula, and although individual statements work, it doesn't work as a whole - it doesn't return any 'Reds'
=IF([Delivery Date]9 < (DATE(2020, 8, 20)), "Green", IF([Delivery Date]9 >= (DATE(2020, 8, 20)), "Yellow", IF([Delivery Date]9 >= (DATE(2020, 8, 27)), "Red")))
Help would be very much appreciated
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Try swapping your Yellow and Red.
=IF([Delivery Date]9 < DATE(2020, 8, 20), "Green", IF([Delivery Date]9 >= DATE(2020, 8, 27), "Red", IF([Delivery Date]9 >= DATE(2020, 8, 20), "Yellow")))
Come see me at ENGAGE 2023! If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth. I will also be doing a short demonstration in the Experience Hub Theater on Tuesday!
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try swapping your Yellow and Red.
=IF([Delivery Date]9 < DATE(2020, 8, 20), "Green", IF([Delivery Date]9 >= DATE(2020, 8, 27), "Red", IF([Delivery Date]9 >= DATE(2020, 8, 20), "Yellow")))
Come see me at ENGAGE 2023! If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth. I will also be doing a short demonstration in the Experience Hub Theater on Tuesday!
-
LisaB:-) ✭✭That's got it - thanks Paul!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help!️
Come see me at ENGAGE 2023! If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth. I will also be doing a short demonstration in the Experience Hub Theater on Tuesday!
Help Article Resources
Categories
Check out theFormula Handbook template!
Field formula is in= 'Summary', So, if none of these conditions are met, copy itself. Not sure if this is possible.<\/p>
=IF([Issue Type]@row= \"Coordinator Request\",[Coordinator Request Types]@row, IF([Issue Type]@row= \"Standard Request\", [Ops Requests]@row, IF([Issue Type]@row= \"Enhancement Request\", [Ops Requests]@row, IF([Issue Type]@row= \"Change Request\", [Ops Requests]@row, Summary@row ))))<\/p>"},{"commentID":395596,"body":"
No. That isn't possible. You will need to put the formula into another column that isn't being reference by the formula itself.<\/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":110334,"type":"question","name":"Question on Function Syntax using IF, AND, and OR","excerpt":"I am trying to get an At Risk check box to return a red flag if there is a date in End Date and that date is earlier than the curent date and the Status cell shows one of three statuses. Can't quite get the formula logic. Any suggestions would be wonderful. Thanks! Desired results: End Date is blank = no flag End Date is…","snippet":"I am trying to get an At Risk check box to return a red flag if there is a date in End Date and that date is earlier than the curent date and the Status cell shows one of three…","categoryID":322,"dateInserted":"2023-09-15T15:25:42+00:00","dateUpdated":"2023-09-15T16:08:50+00:00","dateLastComment":"2023-09-15T17:42:25+00:00","insertUserID":151465,"insertUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-09-15T18:00:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"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-09-15T19:03:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3389593687,"url":"https:\/\/community.smartsheet.com\/discussion\/110334\/question-on-function-syntax-using-if-and-and-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110334\/question-on-function-syntax-using-if-and-and-or","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":110334,"commentID":395594,"name":"Re: Question on Function Syntax using IF, AND, and OR","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/395594#Comment_395594","dateInserted":"2023-09-15T17:42:25+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-09-15T19:03:51+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-09-15T17:02:14+00:00","dateAnswered":"2023-09-15T16:13:23+00:00","acceptedAnswers":[{"commentID":395571,"body":"
Try this:<\/p>
=IF(AND([End Date]@row <> \"\", [End Date]@row < TODAY(), Status@row <> \"Complete\", Status@row <> \"Executed\", Status@row <> \"Recorded\"), 1)<\/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":110322,"type":"question","name":"Trying to add a condition to an IF statement","excerpt":"Hi, all, I'm trying to combine two statements and I'm getting an invalid data type argument. Here are the two formulas: =IF(COUNTIFS({2023 Teams & POs Range 2}, @cell = [PO #]@row, {2023 Teams & POs Range 1}, @cell = 1) > 0, \"yes\", \"no\") ^This formula pulls a value from a checkbox on the 2023 Teams & POs sheet. Right now…","snippet":"Hi, all, I'm trying to combine two statements and I'm getting an invalid data type argument. Here are the two formulas: =IF(COUNTIFS({2023 Teams & POs Range 2}, @cell = [PO…","categoryID":322,"dateInserted":"2023-09-15T11:29:35+00:00","dateUpdated":"2023-09-15T13:30:58+00:00","dateLastComment":"2023-09-15T14:25:16+00:00","insertUserID":166603,"insertUser":{"userID":166603,"name":"Cat C","url":"https:\/\/community.smartsheet.com\/profile\/Cat%20C","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocI1mAktdCp7hdxDfB0DfePr4_ZmUWOXxMFRWOO__RRf=s96-c","dateLastActive":"2023-09-15T15:19:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166603,"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-09-15T19:03:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3389567091,"url":"https:\/\/community.smartsheet.com\/discussion\/110322\/trying-to-add-a-condition-to-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110322\/trying-to-add-a-condition-to-an-if-statement","format":"Rich","lastPost":{"discussionID":110322,"commentID":395543,"name":"Re: Trying to add a condition to an IF statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/395543#Comment_395543","dateInserted":"2023-09-15T14:25: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-09-15T19:03:51+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-09-15T22:03:07+00:00","dateAnswered":"2023-09-15T13:24:47+00:00","acceptedAnswers":[{"commentID":395531,"body":"
You just have a misplaced parenthesis. Move one from the very end to immediately after \"case study\" so that you close out the AND function before moving on to the output of the IF function.<\/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":[]}">
