RYGG - Formula has me STUMPED

@emipathy

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. 

James McCombs

Hey Emipathy,

 

It looks like your "Gray" status comes in before your Value_If_False for the original if statement. Could you try the following:

=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"))), "Gray")

 

@emipathy

Awe yes, praise be! This worked perfectly. 



After I took an hour break from it, I was thinking something like that might be the case. 



Thank you for solving this mystery