Automating RYG balls based on Due date and completion status

clairee42221

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

Chris 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