If Date is between Today and 4 Days from Today = YELLOW

I've been looking at the different threads for an example that I can use for this formula but could not find anything that helps.

What I'm looking for is if the END DATE is less than today, RED. If its greater than today and less than 4 days in the future, YELLOW, otherwise GREEN. I'm getting YELLOW for any date <= TODAY. END DATES for 10/24/22 should be GREEN.

=IF([End Date]4 < TODAY(), "Red", IF([End Date]4 >= TODAY(-4)), "Yellow", "Green")

Find more posts tagged with

Accepted answers

All comments

Sameer K

This may help,

=IF([End Date]@row < TODAY(), "Red", 
    IF([End Date]@row - TODAY() < 4, "Yellow", "Green")
)

Reggie Andaya

@Sameer K Thank you so much, That worked.

edit:

If I wanted to add an extra step, to say add the color code only if Status3 is not COMPLETE. If STATUS is COMPLETE, then no color code. This didn't work for me.

IF([End Date]3 < TODAY(), "Red", NOT(Status@row = "Complete"), IF([End Date]3 - TODAY() < 4, NOT(Status@row = "Complete")"Yellow", IF([End Date]3 - TODAY() > 4, NOT(Status@row = "Complete")"Green"), IF(Status@row = "Complete")""))

Sameer K

In that case it will be,

=IF(Status@row <> "Complete",
    IF([End Date]@row < TODAY(), "Red", 
        IF([End Date]@row - TODAY() <4, "Yellow", "Green")
    )
)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.