IF AND NOT

carly

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"

Samuel Mueller

@carly try this formula:

=IF(OR(TODAY()<[Target Start Date]@row, [Status]@row = "Complete"), "Green", IF(and(TODAY()>=[Target Start Date]@row, TODAY()<=[Target End Date]@row), "Yellow", “Red"))

Also please review this help article on the today formula, it could impact you and you may want to rework your formula.

Automatically update the TODAY function in formulas | Smartsheet Learning Center