How to change my formula to flag yellow on due dates instead of red.
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:
Answers
-
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
-
That worked, thank you! @John_Foster
-
@Alicia D you are welcome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!