Changing Status Dot Color Based On Deadline Column

n_org

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.

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 < =

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 < =