if-then-else logical function

edited 12/09/19 in Smartsheet Basics

I have a symbol column valued ( green, yellow,red). I want to set the color based on a logical formula as follows:  There is Start and a Finish date columns.


if ((Start <= Finish - 8, "Green"), IF(AND(Start > Finish - 8 , Start <= Finish), "Yellow"), IF(Start > Finish, "Red")

Anyone, can you correct this? Thanks





  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Pretty good start! 

    There were a couple issues. 1) You weren't indicating a row number. I have coded this formula to be used in row 24. 2) You were closing each portion of the IF statement, thus ending it early. You have to nest the IF statements within each other and close them at the end. You can see that in the second statement that I closed the AND but didn't close the IF statement till the end.

    Here is the revised formula: 

    =IF(Start24 <= Finish24 - 8, "Green", IF(AND(Start24 > Finish24 - 8 , Start24 <= Finish24), "Yellow", IF(Start24 > Finish24, "Red")))


  • worked nicely, thanks. Red condition can't be met because Start is never allowed to be greater that Finish. I went to project settings, and it didn't clarify the attributes of Start and Finish. Thanks again

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 01/26/18

    What about if today's date is greater than the finish date? Would you want that red? If so, try this: 

    =IF(Start24 <= Finish24 - 8, "Green", IF(AND(Start24 > Finish24 - 8 , Start24 <= Finish24), "Yellow", IF(Today() > Finish24, "Red"))) 

    Just a thought.