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:

Screen Shot 2024-01-19 at 8.30.54 AM.png

Find more posts tagged with

Formulas

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.