Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Help with an IF statement

Bill Brandt
Bill Brandt ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

I am trying to get the R/Y/G/B status to change based on some other cell values and am using this

 

=IF(([End Date]17 - TODAY()) < 1, "Red", IF(([End Date]17 - TODAY()) < 5, "Yellow", IF((ISBOOLEAN(Complete17)), "Blue", "Green")))

 

The first part works fine. I wan the icon to go blue if the "Completed" checkbox is checked but can't seem to get it to work after many variations of the above. probably a simple sytax error that I am not seeing.

 

thanks

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Bill,

     

    Try this:

     

    =IF(Completed17, "Blue", IF(([End Date]17 - TODAY()) < 1, "Red", IF(([End Date]17 - TODAY()) < 5, "Yellow", "Green")))

     

    I believe you want Blue if the item is complete, regardless of date. So I moved Blue to the first IF check.

    IF statements work until they find a condition and then stop. 

    If the date is in the past (red) or within 5 days (yellow), then the formula does not care about the Complete column value in your formula.

     

    You also called the Completed column a check box column. If so, you don't need ISBOOLEAN. That checks if the value is a BOOLEAN (which it should be, since it is a check box.  Rather, you want to see the value Completed17.

     

    Lastly, you called the check box column "Completed" in your text but "Complete" in your formula. I assume that was a typo and the column is Completed, so that is what my formula uses. You can copy and paste out of the formula celll instead of retypiing next time.

     

    Hope this helps.

     

    Craig

     

  • Bill Brandt
    Bill Brandt ✭✭✭✭✭

    Thanks,

     When IF statements are flat like that I tend to forget the order of execution.

     

    Bill

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    If there are more than 2 nested if's, I tend to write psuedo code to follow them later.

     

    if (this) then

      do this

    else if (this other thing) then

      do this

    else if (this other thing #2) then

      do this

    etc...

     

    Helps a bit on the first time and loads when I have to come back and fix/change it.


    Craig

This discussion has been closed.