Automate Harvey ball color based on multiple variables

Options
Eric R
Eric R
edited 12/09/19 in Formulas and Functions

I'm not sure if what I want to do is feasible or not so please let me know if I'm going off the deep end. I would like to have a Harvey ball change color based on several criteria/variables to show likelihood the task will, or won't be, completed on time.

Has anyone made a formula that will show a red Harvey ball if the task is through, lets say, 75% of the duration and % complete is less than %50 (or something along those lines)? Then criteria for yellow and green?

Is it possible to include a variable of whether or not the task was started on time?

If anyone has already done something along these lines I would love to see what you have rather than reinvent the wheel. 

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Hi there, you can use IF statements intermingled with AND statements to make the formula you are looking for. But I don't have time to figure the duration percentage... you'll need to develop that...

    =IF(AND([duration % column]1 < .75, [% complete]1 < .5), "Red", IF(And... 

    build it out that way. But you have to have either a formula or column calculating your duration to check the % of time. 

  • Eric R
    Options

    Thanks for pointing me in the right direction!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Sure thing! Let me know if you encounter any issues or if you have any questions. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!