Automating RYG "Light Status" for Dates using IF AND Formulas
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")))
Help Article Resources
Check out the Formula Handbook template!