RYGG - Formula has me STUMPED

Options
@emipathy
@emipathy ✭✭✭
edited 12/09/19 in Smartsheet Basics

Situation: I'm creating a "Health Awareness Column" on our production trackers. The point is that at-a-glance, it'll show you, for due dates in this week or the coming week, what level of complete the column is. 

It is working great in all instances, EXCEPT, when the End Date is outside of the same-week or next-week range. 



Here's my formula: 

=IF(OR(WEEKNUMBER([End Date]@row) = WEEKNUMBER(TODAY()) + 1, WEEKNUMBER([End Date]@row) = WEEKNUMBER(TODAY())), IF(Status@row >= 0.75, "Green", IF(AND(Status@row >= 0.5, Status@row < 0.75), "Yellow", IF(OR(Status@row < 0.5, Status@row >= 0), "Red", "Gray"))))

 

I thought this would produce a grey circle if it was outside of the week-or-next range. However, it does not do that. See the picture below for the black-filled row, you can see the formula is working below it (red circle). Note: today's date is September 17th. 

Any help would be appreciated, this formula has gone through a bunch of edits and it does all I want it to, except in that one case. 

SS_Example_HealthFormula.JPG

Comments