if-then-else logical function

jrehm35926

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

 

Art

 

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")))

 

jrehm35926

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

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.