RYG IF Statement Help

Millie54!!

Hello-I am working to automate a SmartSheet for my team and have a "Status" column with RYGs (Red, Yellow, Green) which are visual assists for the status of the action item.  I need help; the IF statement I created won't work.

Here is what I need:

IF the "Completed" column (based on a date entry) is populated with a date, the RYG should turn green.  IF the "Scheduled" column has a date, the RYG should turn yellow.  Otherwise, the RYG will be Red if both the "Completed" and "Scheduled" columns are blank.

Hoping you can help-thanks so much!

Millie

Andrée Starå

Hi Millie,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Millie54!!

Hello!  Thanks so much for your response-greatly appreciated!

 

Here is a screenshot of my workbook with the color status examples. 

The RYG is green because a completed date was entered and the RYG is yellow because a scheduled date was entered to show "action" was taken. 

I need an IF statement that will make the RYG's change color automatically based on the "Scheduled" date entered (Yellow) and the "Completed' date entered (Green).

Best, Millie

 

Mike Wilday

Try this one. Does it work? 

=IF(AND(ISBLANK(Scheduled@row), ISBLANK(Completed@row)), "Red", IF(ISBLANK(Completed@row), "Yellow", "Green"))

 

Millie54!!

YES!!  YOU ROCK!!  Thanks so much-the support is outstanding 

Andrée Starå

I'm always happy to help!

Mike beat me to it! 

Best,

Andrée

Mike Wilday

Glad that works for you. 

Paul Newcome

You could also work it in the opposite direction...

 

=IF(ISDATE(Completed@row), "Green", IF(ISDATE(Scheduled@row), "Yellow", "Red"))

 

Not to say that Mike is wrong. Just showing that there can be more than one solution. I find that keeping that in mind can make more complex formulas much easier when you are able to consolidate/condense/simplify.

 

In this particular case it doesn't make much difference though. Just a little tid-bit of info for future formula attempts.