Help with a date / Harvey ball formula

Sha Angel

Hi - Smartsheet Community,

I'm trying to setup a similar formula but using a date column. I have a column titled Revision Date. I would like to add a column titled Status for the Harvey balls (red, yellow and green) to indicate the following:

If revision date is 365 days older than current calendar date display a red Harvey ball.

If revision date is 275 to 364 days older than current calendar date display a yellow Harvey ball.

If revision date is 1 to 274 days older than current calendar date display a green Harvey ball.

Any help is greatly appreciated. Thank you - Sha

Mike Raposo

Hi Sha,

I believe the following formula should work for the case you described:

=IF([Revision Date]@row <= TODAY(-365), "Red", IF(AND([Revision Date]@row <= TODAY(-275), [Revision Date]@row > TODAY(-365)), "Yellow", IF(AND([Revision Date]@row <= TODAY(), [Revision Date]@row > TODAY(-274)), "Green")))

Hope this helps! Let me know if you have any questions.

Best,

Mike