Automating RYG balls based on Due date and completion status

edited 12/09/19 in Using Smartsheet
04/16/18 Edited 12/09/19

Im having an issues creating this formula and was hoping someone could help improve on my formula. 

Im trying to create a HEALTH flag marked "Green" if the task is not due today, "Yellow" if the task is due today, and "Red" if the task is past due.

However, the HEALTH flag should stay blank if: 





1) the STATUS column is "complete"

2) the FINISH column is blank



 

I attached a picture of the formula i'm currently using. Thank you in advance for the help! 

 

Also see the formula here 

=IF(Finish6 < Today6, "Red", IF(Finish6 = Today6, "Yellow", IF(Finish6 > Today6, "Green")))

SmartSheet2.png

Comments

  • Chris McKayChris McKay ✭✭✭✭✭

    Hi Claire,

    This should do the trick:

    =IF(Finish6 < Today6, "Red", IF(Finish6 = Today6, "Yellow", IF(Finish6 > Today6, "Green", IF(AND(Status6 = "complete", ISBLANK(Finish6)),"",""))))

    If you 're looking to make the blank status trigger if either of the 2 additonal criteria you specified are true, then simply change the AND to an OR.

    Kind regards,

    Chris McKay

Sign In or Register to comment.