Complex nested IF, AND with traffic light symbols
I'm working on automating a symbol highlighter to indicate project progress as well as alerting if key milestones are being compromised. The formula below is structured based on the following columns:
- Finish: The Activity completion date
- Start: The activity start date
- Date: A column containing fixed dates to which certain activities must be completed (these are blank for the most part, but key milestones will be entered into this column).
I'm using the symbols Red/Green/Yellow/Gray, and what I'm trying to achieve is:
- If the Finish date is in the past, set the symbol to Green (assume activity is complete)
- If TODAY() is between start and finish dates, set the symbol to Yellow (assume the activity is in progress)
- If the Start date is in the future, set the symbol to Gray (assume the activity is not started yet)
- Finally, if the Fixed date is less than the finish date (and not blank), set the symbol to Red (assume the milestone is compromised)
Steps 1-3 above works in the formula below, but I'm struggling to get the final step to work, and as I'm not too familiar with these formulas, I have not been able to get this to work from browsing the support and would appreciate any help to finalize this.
Finish1 < TODAY(), "Green",
IF(AND(Start1 <= TODAY(), Finish1 >= TODAY()), "Yellow",
IF(Start1 > TODAY(), "Gray",
IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"