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

• ✭✭✭✭✭✭

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

Come Say Hello!

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

Come Say Hello!

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

• ✭✭✭✭✭✭

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

Ryan Sides