Help with RGBY Balls

RJ Sparks

Hi Community,

I have tried everything I know and I haven't been able to figure this out.  Below is my formula

=IF([% Complete]31 = 1, "Blue", IF(TODAY() - Finish31 > 0, "Red", IF(TODAY() - Finish31 > -4, "Yellow", IF(TODAY() - Finish31 > -5, "Green"))))

What I'd like to have happen is if % Complete = 100%, Blue, If Today - Finish Date GREATER THAN 0 , Red, If Today - Finish Date GREATER THAN -4, Yellow , If Today - Finish Date LESS THAN -3, GREEN.

I'm trying to default any tasks that are less than - 4  (-5, -6, -7) days from the finish date to GREEN.  I'm sure it's something small that I'm doing wrong, but I appreciate any help.

 

Thanks

 

eric.o

Hello,

 

Happy to help! To clarify, by calculating Today - Finish date, all Finish dates in the future, as in tomorrow, will be in the negative and all Finish dates in the past, as in yesterday, will be positive. If you desire this to be reverse you will want to reverse the order and the greater than/less than symbols. Similar to this, Finish date-Today.

 

The current formula: 

 

=IF([% Complete]31 = 1, "Blue", IF(TODAY() - Finish31 > 0, "Red", IF(TODAY() - Finish31 > -4, "Yellow", IF(TODAY() - Finish31 > -5, "Green"))))

 

In the above provided formula, the status will only appear 'Red' if the value is greater than 0 and not including 0, thus, 1, 2, 3 etc. It will only appear 'Yellow' if the value is greater than -4 and not including -4, thus -3, -2, -1, & 0. In the above formula, nothing can be greater than -5 except -4 because all other negative numbers are displayed as another color. To correct this one part, flip the greater than to a less than. This will include all numbers after -5 such as -6, -7, etc to display "Green". The formula would look like this: 

 

=IF([% Complete]31 = 1, "Blue", IF(TODAY() - Finish31 > 0, "Red", IF(TODAY() - Finish31 > -4, "Yellow", IF(TODAY() - Finish31 < -5, "Green"))))

 

Please let us know if you have any questions on the above.

 

Cheers, 

Eric  

Smartsheet Support