Harvey Ball Formula

steve50951 · 02/22/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

Screen Shot 2019-02-22 at 2.51.56 PM.png

Brian W · 02/22/19

Try this:

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

Nic Larsen · 02/22/19

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")))

steve50951 · 02/25/19

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.

Screen Shot 2019-02-25 at 10.05.23 AM.png

Screen Shot 2019-02-25 at 10.05.42 AM.png

Screen Shot 2019-02-25 at 10.05.52 AM.png

Screen Shot 2019-02-25 at 10.06.04 AM.png

Nic Larsen · 02/25/19

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"))))

steve50951 · 02/25/19

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.

Nic Larsen · 02/25/19

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"))))

steve50951 · 02/25/19

YES!!! thanks!

SGF

Harvey Ball Formula

Comments

Help Article Resources

Categories