Changing Status Dot Color Based On Deadline Column

Hi, I've tried looking up other questions to answer this and got as far as this formula [=IF(Deadline@row <= TODAY(+14), "Green", IF(Deadline@row <= TODAY(+7), "Yellow", IF(Deadline@row >= TODAY(0), "Red", "")))], but it's not providing the proper results. I have a Deadline column and a Status column (which is the RGY dots). I would like the dot to change color based on the following parameters:

If the deadline is approaching within the next 14 days or more, change to green.

If the deadline is approaching within the next 7 days, change to yellow.

If the deadline is today and/or in the past, change to red.

With my erroneous formula above, no matter what date is in there, the dot is green.

Find more posts tagged with

Formulas

Conditional Formatting

IF(OR

IF(

Accepted answers

heyjay

https://community.smartsheet.com/discussion/comment/444403#Comment_444403

try this..

=IF(AND(Deadline@row > TODAY(), Deadline@row < TODAY(+7)), "Yellow", IF(Deadline@row > TODAY(+8), "Green", IF(Deadline@row <= TODAY(0), "Red", "")))

Based on how I understand your initial formula and why its causing an issue-

heyjay

replace it with = > and < =

All comments

heyjay

<= TODAY(+14) covers <= TODAY(+7)

You'll want to use:

-Between today and less than or equal to 7 days. (Yellow)
-and More than 7 days (Green)
-less than today (Red)

Using is equal to more than once might have an issue with your formula.

e:formatting

heyjay

Im not sure why my response is formatting like that. Sorry I didnt mean to. I tried to remove it but it keeps doing it.

Here's what I came up with:

=IF(AND(Deadline@row > TODAY(), Deadline@row < TODAY(+7)), "Yellow", IF(Deadline@row > TODAY(+8), "Green", IF(Deadline@row <= TODAY(0), "Red", "")))

n_org

@heyjay, I'm confused about the response. What changes to my formula should I be making?

heyjay

https://community.smartsheet.com/discussion/comment/444403#Comment_444403

try this..

=IF(AND(Deadline@row > TODAY(), Deadline@row < TODAY(+7)), "Yellow", IF(Deadline@row > TODAY(+8), "Green", IF(Deadline@row <= TODAY(0), "Red", "")))

Based on how I understand your initial formula and why its causing an issue-

n_org

https://community.smartsheet.com/discussion/comment/444406#Comment_444406

That fixed it! Thank you!

n_org

It turns out, the formula isn't working for some of the periods of time in the future. (It looks like it returns a blank instead of a dot for 7 and 8 days into the future. Anyone have any ideas for a fix? Here's what I'm using:

=IF(AND(Deadline@row > TODAY(), Deadline@row < TODAY(+7)), "Yellow", IF(Deadline@row > TODAY(+8), "Green", IF(Deadline@row <= TODAY(0), "Red", "")))

heyjay

replace it with = > and < =

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.