New User Need Formula Assistance

I am a new user to smartsheet and I am trying to calculate project health for a project based off of Due date, and % complete. I keep getting different errors either unparseable or incorrect argument set. I have tried to look at past posts but cannot seem to get a formula to work.

Appreciate any assistance.

I have Harvey balls set to be Gray, Green, Yellow, Red

I am trying to have it be Gray if start date is blank (project isn't started)

Green if today is less than due date and % complete is less than 1, or if % complete =1

Yellow if 15 days from today is the due date and % Complete is =< 50%

Red if 8 days from today is the due date and % complete is =<75%, or if due date is Today

Here is the formula I have been working but not sure where I am going wrong.

=IF(AND(TODAY() < [Due Date]@row, [% Complete]@row < 1, "Green", IF(AND(TODAY() < [Due Date]@row, [% Complete]@row <= 0.5), "Yellow", IF(AND(TODAY() <= [Due Date]@row, [% Complete]@row < =0.6, "Red")))))

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓

    Hi @Steve Klotz , this one took awhile! Now I need a cup of coffee haha Try this one...

    =IF([Start Date]@row = "", "Gray", IF(AND([Due Date]@row <= TODAY(15), [Due Date]@row >= TODAY(8), [% Complete]@row <= 0.5), "Yellow", IF(OR(AND([Due Date]@row <= TODAY(7), [Due Date]@row > TODAY(), [% Complete]@row <= 0.75), AND([Due Date]@row <= TODAY(), [% Complete]@row <> 1)), "Red", "Green")))

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!