IF AND NOT

Options

Need help with some formulas

Important Columns : Schedule Health, Status, Target Start Date, Target End Date

Schedule Health will hold the formula - changing to RED GREEN and YELLOW Depending on formula

Green : IF Today is < than Target Start Date, AND/OR Status = "Complete"

Yellow = IF Today is >= Target Start Date, AND Today <= Target End Date AND Status is NOT "Complete" (Not sure if the Green status of "Complete" would override the need for the last AND/NOT)

RED = IF Today is < Target End date AND Status is not = "Complete"


I've tried a multitude of things and just cant seem to get it, please any help would be great!


Heres what I tried that I thought would work:

=IF(TODAY()>[Target Start Date]@row, AND(TODAY()<[Target End Date]@row, AND(NOT([Status]@row = "Complete"), "Yellow”, IF(TODAY()<[Target Start Date]@row OR([Status]@row = "Complete"), "Green", IF(TODAY()>[Target End Date]@row, “RED"

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!