Automating RYG balls based on Due date and completion status

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

Find more posts tagged with

Templates

Community Platform

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Submitting forms for approval
Any help would be greatly appreciated! We have a 3-step process for travel approvals. I am able to get the forms to the correct people. I now have a request for the ability to add comments during the approval process. Staff submit request via Smartsheet form Automation forwards the form to the manager for approval Once…
Why would an automation for movement not carry over a cell value?
Hi everyone, I’m running into an issue with one of my sheets and was hoping to see if anyone else has experienced this. Here’s the setup: I have a sheet where, when I check a box, the row moves to a “Completed” sheet. In my Total column, I use a formula that sums a group of cells tied together via a parent/child…
Unable to edit smartsheet forms without constant reboot
Is anyone having trouble with smarthsheet over the past several days? Every time I go into a form to edit it, the cursor jumps fields after each character is typed. It is so frustrating. In some cases the cursor gets frozen in a field and jumps back and forth between two fields and freezes the whole system. I have yet to…