# Harvey Ball Formula

edited 12/09/19

Needing a formula to turn Harvey Red, Yellow, and green.

If sold box is checked = green. If Quote Submitted day is over 14 days, AND sold is NOT CHECKED = yellow. And if Quote submitted date is over 30 days past, AND SOLD is not checked = red.

I have the green part correct

=IF((Sold@row = 1, "Green").

Having trouble with putting in the other conditions.

thanks so much for all your help

SGF

Tags:

## Comments

• ✭✭

Try this:

=IF(Sold@row = 1, "Green", IF([Quote Submitted Date]1 < TODAY(-30), "Red", IF([Quote Submitted Date]1 < TODAY(-14), "Yellow")))

• ✭✭✭✭✭✭

Try this:

=IF(Sold@row = 1, "Green", IF(AND(Sold@row = 0, [Quote Submitted Day]@row >= TODAY(-14)), "Yellow", IF(AND(Sold@row = 0, [Quote Submitted Day]@row > TODAY(-30)), "Red")))

• so I changed to the 4 color Harvey ball. 3 of the 4 colors work, but the red color comes up as incorrect argument set.

Here's the formula im using.

=IF(Sold@row = 1, "Green", IF(AND(Sold@row = 0, [Quote Submitted Date]@row >= TODAY(-7)), "Gray", IF(AND(Sold@row = 0, [Quote Submitted Date]@row >= TODAY(-14)), "Yellow", IF(AND(Sold@row = 0, [Quote Submitted Date]@row >= TODAY(-30), "Red")))))

See screenshots below.

• ✭✭✭✭✭✭

I've added the 4th color, I'm not getting an error on this one:

=IF(Sold@row = 1, "Green", IF(AND(Sold@row = 0, [Quote Submitted Day]@row >= TODAY(-7)), "Gray", IF(AND(Sold@row = 0, [Quote Submitted Day]@row >= TODAY(-14)), "Yellow", IF(AND(Sold@row = 0, [Quote Submitted Day]@row > TODAY(-30)), "Red"))))

• Perfect. Formula worked great!

Thanks for helping me get this on. last thing for this one (hopefully)...If the date is set past the 30 days the cell goes blank. what would I need to do to keep the Harvey ball red for anything over 30 days?

I need to to stay red until sold is checked.

thanks again.

• ✭✭✭✭✭✭

This should do it:

=IF(Sold@row = 1, "Green", IF(AND(Sold@row = 0, [Quote Submitted Day]@row >= TODAY(-7)), "Gray", IF(AND(Sold@row = 0, [Quote Submitted Day]@row >= TODAY(-14)), "Yellow", IF(AND(Sold@row = 0, [Quote Submitted Day]@row > TODAY(-30)), "Red", "Red"))))

• YES!!! thanks!

SGF

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!