Status Balls & Date formula

System

This discussion was created from comments split from: Help with IF 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

Genevieve P.

Hi @Sha Angel

Try this:

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

Since logic formulas read left-to-right, this means that it will only move on to the "Yellow" ball if the date is more recent than a year ago, so I didn't have to add in your second criteria.

You'll need to adjust the [Revision Date] to be the name of your Revision Date column, if it's different. Keep in mind that blank cells will be read as older than a year ago, so this will return a red ball. To change this you could add in another criteria of what to do if the cell is blank - let me know if you need help with this.

Here are some Help Center articles that I used:

• IF function
• TODAY function
• @row function
• Formula Operators (< and >)
• Formula FAQs

Sha Angel

Thank you for the information. You offered to explain how to address a blank cell. I know I would need to select the four harvery ball option but how to I change the formula to make a blank cell (no date) show with a blue status ball?

Thank you.

Genevieve P.

Hi @Sha Angel

Happy to help!

Try this:

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

I put the blank rule first. Then if the cell is not blank, the formula will read the next statement. Let me know if you need help changing the column to include the blue ball!

Cheers,

Genevieve