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

Alicia D

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:

John_Foster

Hi @Alicia D,

I believe the below should work, I have removed an equals in one of the fields and added another nested IF statement. Let me know how you get on.

=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(AND([End Date]@row = TODAY(0), ISDATE([End Date]@row)), "Yellow", IF(OR(ISBLANK([End Date]@row), ISBLANK([Start Date]@row)), "Green", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))))))

Hope this helps!

John

Alicia D

That worked, thank you! @John_Foster

John_Foster

@Alicia D you are welcome!