Yellow Code Not Working in Formula

delaurellc
delaurellc ✭✭✭
inFormulas and Functions

I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with the way I have sestup the today function I have tried both =TODAY(2) and >=TODAY(2) niether seems to work.

如果(和([日期]@row =今天(2),统计us@row <> "Complete"), "Yellow"

I need it to do the following if the due date is less than today Red. if it is today tomorrow and the next day it is Yellow, if it is in the future three days or more green. As I said the red and green work fine.

Full Code

=IF(AND([Due Date]@row < TODAY(), Status@row <> "Complete"), "Red", IF(AND([Due Date]@row = TODAY(2), Status@row <> "Complete"), "Yellow", "Green"))

Thanks,

Caroline

· Share on Facebook Share on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row>= TODAY(3), "Green", "Yellow"))


    The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:

    =IF(Status@row <> "Complete", IF(OR([Due Date]@row = "", [Due Date]@row.= TODAY(3)), "Green", IF([Due Date]@row< TODAY(), "Red", "Yellow"))


    I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.


    Of course there are a number of other ways to do it, but this would be my personally preferred method.

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row>= TODAY(3), "Green", "Yellow"))


    The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.

    10xViz.com

    · Share on Facebook Share on Twitter
  • delaurellc
    delaurellc ✭✭✭

    Hi Paul.

    anything less than today should be red

    anything today tomorrow and the next day yellow so today+2 days

    anything 3+ days should be green

    Caroline

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Right. Did you plug it in and try it?


    When Smartsheet is evaluating a nested IF statement, it reads from left to right and stops on the first true value. So if it makes it to the second IF then it is already known that the first is not true. We can leverage this by saying "if it is less than today then output red, if it is greater than or equal to today +3 then output green, and everything else is yellow."

    If it passes by both red and green in the above, then it must be greater than or equal to today by passing red but also less than today +3 by passing over the green.


    =IF(Status@row <> "Complete",IF([Due Date]@row< TODAY(), "Red",IF([Due Date]@row>= TODAY(3), "Green", "Yellow"))

    If it makes it past the bold portion, then the date MUST be either today or in the future.


    =IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red",IF([Due Date]@row>= TODAY(3), "Green","Yellow"))

    If it makes it past his bold portion, then we already know it is greater than today because it made it this far to begin with, and we know it is less than today +3 because it isn't flagging as true. So we output yellow.


    =IF(Status@row <> "Complete",IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row>= TODAY(3), "Green", "Yellow"))

    这里的大胆的部分基本上是说the nested RYG IF formula if the Status is not Complete. This saves us from having to repeat in with an AND function for each of the colors.


    Using the same logic as outlined above where we read from left to right and stop on the first true value, the same outcome could be accomplished using this formula:

    =IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(2), "Yellow", "Green"))


    Or this one:

    =IF(AND(NOT(Status@row = "Complete"), [Due Date]@row< TODAY()), "Red", IF(AND(NOT(Status@row = "Complete"), [Due Date]@row>= TODAY(), OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1), [Due Date]@row = TODAY(2))), "Yellow", IF(AND(NOT(Status@row = "Complete"), [Due Date]@row>= TODAY(3)), "Green")))


    Or quite a few other ways to get the same outcome. I personally just like to establish the far ends (in this case red and green) and just say that anything that isn't one of those must be in the middle.

    10xViz.com

    · Share on Facebook Share on Twitter
  • delaurellc
    delaurellc ✭✭✭

    Paul,

    This is good, but can you tell me how to add one more item to this code

    If the due date is blank it needs to be green

    Where would I add this?

    =IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red",IF([Due Date]@row>= TODAY(3), "Green","Yellow"))

    Caroline

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:

    =IF(Status@row <> "Complete", IF(OR([Due Date]@row = "", [Due Date]@row.= TODAY(3)), "Green", IF([Due Date]@row< TODAY(), "Red", "Yellow"))


    I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.


    Of course there are a number of other ways to do it, but this would be my personally preferred method.

    10xViz.com

    · Share on Facebook Share on Twitter
  • delaurellc
    delaurellc ✭✭✭

    Not working

    I have tried the blank this way multiple times and it does not work

    [Due Date]@row = ""

    I have always had to sue the ISBLANK funtion but not sure how to add to this code, no matter what I do not working

    · Share on Facebook Share on Twitter
  • delaurellc
    delaurellc ✭✭✭

    Paul,

    I was able to get it working with your code working for some reason it copied a period where the greater than symbol was suppost to be.

    Thank you as always,

    Caroline

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️


    When you type something like

    [Due Date]@row = ""

    into a formula and it doesn't work, exactly what are you typing, and are you using some other program such as Word to type out formulas?


    The "" part is quote quote with no space. If you are typing quotes in another program, that program could be using what are called "smart quotes". If you do a search here in the Community, you will find quite a few posts explaining them. I just haven't had enough coffee yet this morning to make it coherent. Haha.

    10xViz.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 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":[]},{"discussionID":112211,"type":"question","name":"Automation Alert Row Info","excerpt":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?","snippet":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable…","categoryID":322,"dateInserted":"2023-10-26T15:56:15+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T16:07:33+00:00","insertUserID":169126,"insertUser":{"userID":169126,"name":"J.Sanabria","url":"https:\/\/community.smartsheet.com\/profile\/J.Sanabria","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:52:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":72271,"lastUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":17,"score":null,"hot":3396672828,"url":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","format":"Rich","lastPost":{"discussionID":112211,"commentID":401856,"name":"Re: Automation Alert Row Info","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401856#Comment_401856","dateInserted":"2023-10-26T16:07:33+00:00","insertUserID":72271,"insertUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05: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-26T17:52:33+00:00","dateAnswered":"2023-10-26T16:07:33+00:00","acceptedAnswers":[{"commentID":401856,"body":"

@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/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":112203,"type":"question","name":"Help w\/ Sumif date is before today","excerpt":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…","snippet":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I…","categoryID":322,"dateInserted":"2023-10-26T14:19:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":130047,"lastUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3396663791,"url":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","format":"Rich","lastPost":{"discussionID":112203,"commentID":401837,"name":"Re: Help w\/ Sumif date is before today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401837#Comment_401837","dateInserted":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+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\/JI13A7YQ5L7E\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T15:04:18+00:00","dateAnswered":"2023-10-26T14:24:37+00:00","acceptedAnswers":[{"commentID":401817,"body":"

Your formula looks good! But is your \"Fiscal Month End\" formatted as a date column? It looks like a text (should have the leading 0's for the months). Double click on the header to change it to a Date type column and you should be good to go!<\/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":[]}">

Trending in Formulas and Functions

  • How to pull the most recent status symbol from another sheet?

    Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date/symbol, that symbol should be automatically updated…

  • Automation Alert Row Info

    Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?

  • Help w/ Sumif date is before today

    Hi, Hitting a snag w/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…