New User Need Formula Assistance

Steve Klotz

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

Ryan Sides

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

Paul Newcome

You will need to clean up some of the closing parenthesis on the end, but it looks like you missed closing the first and third AND functions.

Ryan Sides

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

Steve Klotz

Thanks @Ryan Sides! That appears to work. Much appreciated.

Ryan Sides

@Steve Klotz Glad it's working! Happy to help.