How do I get this to work as I intend? Health Symbol Formula - Red, Yellow, Green, Gray

Lona Kicinski

Hello,

I've worked this many different ways, but cannot figure out how to get this to work. When I reordered the On Hold to the front, I finally got that working but then I saw the Completed < Today was red. I'm sure I'm missing something here and am fairly new to this. Will someone offer a little guidance to point out the error and help find a solution. I've looked at other examples and pulled from that knowledge, but no luck. I just know this is possible.

Here is the idea:

Red - Past the End Date
Yellow - Week before End Date
Green - Any before 1 Week to End Date(basically any that aren't gray, red, or yellow) or Completed
Gray - On Hold

Original that fixed the Gray symbol but discovered Completed is red pending the completion date:

=IF(Status@row = "On Hold", "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))

Attempt 1 that works except for the Completed <Today remains red:

=IF(Status@row = "On Hold", "Gray", IF(Status@row = "Completed", "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))

Attempt 2 again with the Completed <Today remains red:

=IF(Status@row = "Completed", "Green", IF(Status@row = "On Hold", "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))

Attempt 3: with the IF/AND used and moved to differing places and the Compelted<Today remains red:

=IF(AND([End Date]@row < TODAY(), Status@row = "Completed"), "Green", IF(Status@row = "On Hold", "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))

Thank you!

Lona K.

AKnight

Hey Lona!

Try this:

=IF(AND(NOT(Status@row = "Complete"), TODAY() > [End Date]@row), "Red", IF(AND(NOT(Status@row = "Complete"), TODAY(7) > [End Date]@row), "Yellow", IF(Status@row = "On Hold", "Gray", "Green")))

Lona Kicinski

Almost. I think reordering some of the functions may do the trick, but not sure. Completed is good, but On Hold looks to be wonky. I've been working on this for a few days off and on and really appreciate your help! Here is a snippet of the results of your suggested formula.

Before I sent this message, I moved the On Hold to the front, and it looks to be working now! If/And/Not was the trick. I changed the date of the bottom row to test the yellow.

=IF(Status@row = "On Hold", "Gray", IF(AND(NOT(Status@row = "Complete"), TODAY() > [End Date]@row), "Red", IF(AND(NOT(Status@row = "Complete"), TODAY(7) > [End Date]@row), "Yellow", "Green")))

Thank you so much!!!!

AKnight

Hey Lona!

Try this:

=IF(AND(NOT(Status@row = "Complete"), TODAY() > [End Date]@row), "Red", IF(AND(NOT(Status@row = "Complete"), TODAY(7) > [End Date]@row), "Yellow", IF(Status@row = "On Hold", "Gray", "Green")))

Lona Kicinski

Almost. I think reordering some of the functions may do the trick, but not sure. Completed is good, but On Hold looks to be wonky. I've been working on this for a few days off and on and really appreciate your help! Here is a snippet of the results of your suggested formula.

Before I sent this message, I moved the On Hold to the front, and it looks to be working now! If/And/Not was the trick. I changed the date of the bottom row to test the yellow.

=IF(Status@row = "On Hold", "Gray", IF(AND(NOT(Status@row = "Complete"), TODAY() > [End Date]@row), "Red", IF(AND(NOT(Status@row = "Complete"), TODAY(7) > [End Date]@row), "Yellow", "Green")))

Thank you so much!!!!