How to change my formula to flag yellow on due dates instead of red.

Alicia D
Alicia D ✭✭
edited 01/19/24 in Formulas and Functions

I believe this is an easy fix, I am just not able to figure it out! I am trying to change my formula so that the status icon does not change to red only once it is in progress for tasks due today. I would rather it show as yellow if something is due today, then red if it passes its due date. Here is my current formula:

=IF(ISBLANK(Complete@row), "", IF(Complete@row = "Not Started", "Gray", IF(Complete@row = "Complete", "Green", IF(AND([End Date]@row <= TODAY(0), ISDATE([End Date]@row)), "Red", IF(OR(ISBLANK([End Date]@row), ISBLANK([Start Date]@row)), "Green", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))))

Additionally, here is a screenshot of my sheet:



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!