Yellow if, Red if
Hello - looking for some assistance on a formula that I'm trying to create that will return my task health as Yellow if 1 - 13 days beyond target completion date, and Red if 14 days beyond. I have created the below, which works, however I'm stumped on where I need to incorporate the IF formula for Yellow, and how to ensure they are both active:
=IF(ISBLANK(Status@row), "", IF(AND([Target/Actual Completion Date]@row < TODAY(+14), Status@row <> "Complete"), "Red", IF(Status@row = "Not Started", "-", IF(Status@row = "In Process", "Yellow", IF(Status@row = "Complete", "Green")))))
I've tried the below, and it works, but it will only report health as Red regardless of how many days over the task is:
=IF(ISBLANK(Status@row), "", IF(AND([Target/Actual Completion Date]@row < TODAY(+14), Status@row <> "Complete"), "Red", IF(Status@row = "Not Started", "-", IF(Status@row = "In Process", "Yellow", IF(Status@row = "Complete", "Green", IF(AND([Target/Actual Completion Date]@row < TODAY(+1), Status@row <> "Complete", "Yellow")))))
Answers
-
ro.fei ✭✭✭✭✭Hey@ZDevier! It looks like it's only returning red because all you're checking for is if the Target/Actual Completion Date is less than 14 Days after the current date. I believe you may want to switch your less than sign to a greater than sign. If that doesn't work, I would try using the NETDAYS function to set your parameters.
Hope this helps! Let me know if you're still having issues & I'll be happy to help out some more.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!
Help Article Resources
Categories
=TODAY() - IF([Assigned Date]@row <> \"\", [Assigned Date]@row, DATEONLY(Created@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":109212,"type":"question","name":"INDEX MATCH with multiple values question","excerpt":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets. There will be duplicate results for each column…","snippet":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker…","categoryID":322,"dateInserted":"2023-08-21T17:26:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T20:29:09+00:00","insertUserID":165425,"insertUser":{"userID":165425,"name":"MarcM","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MarcM","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T20:24:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3385290352,"url":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","format":"Rich","lastPost":{"discussionID":109212,"commentID":391719,"name":"Re: INDEX MATCH with multiple values question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391719#Comment_391719","dateInserted":"2023-08-21T20:29:09+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+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-21T20:28:55+00:00","dateAnswered":"2023-08-21T19:17:27+00:00","acceptedAnswers":[{"commentID":391690,"body":"
I think I would just use a COUNTIFS in this situation:<\/p>
=IF(COUNTIFS({FY24 Hirevue Response Sheet Range 1}, [Evaluator Name]@row, {FY24 Hirevue Response Sheet Range 2}, [HV Candidate Name]@row) > 0, 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":[]},{"discussionID":109221,"type":"question","name":"Auto populate with current, past, or upcoming text based on year","excerpt":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or \"Upcoming\" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE) =IF(Year@row = YEAR(TODAY()),…","snippet":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or…","categoryID":322,"dateInserted":"2023-08-21T18:28:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165112,"lastUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3385288842,"url":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","format":"Rich","lastPost":{"discussionID":109221,"commentID":391688,"name":"Re: Auto populate with current, past, or upcoming text based on year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391688#Comment_391688","dateInserted":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51: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-21T18:51:50+00:00","dateAnswered":"2023-08-21T18:50:14+00:00","acceptedAnswers":[{"commentID":391687,"body":"
See if this works for you:<\/p>
=IF(Year@row = YEAR(TODAY()), \"Current\", IF(Year@row < Year(today()),\"Past\", IF(Year@row > YEAR(today()), \"Upcoming\", 0)))<\/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":[]}">
