Automating RYG "Light Status" for Dates using IF AND Formulas

Hi all,

I have several tasks that have two columns stating start and complete dates. I would simply want to show that the task status with the RYG symbols:

Red = Not Started, Not Complete

Yellow = Started, but not complete

Green = Complete

The formula works but is not perfect. It seems to 'break' by showing a blank value when I have a complete date listed but no start date. I can simply fix it by populating the start date with a dummy # but I would prefer to leave it blank to show that start date is unknown.

Here is the formula I am using (I've tried removing the 1st logical expression in the beginning but can't get it to work):

=IF(AND(ISDATE([Date Submitted]1), ISDATE([Date Completed]1)), "Yes", IF(AND(ISDATE([Date Submitted]1), ISBLANK([Date Completed]1)), "Hold", IF(AND(ISBLANK([Date Submitted]1), ISBLANK([Date Completed]1)), "No")))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!