Health symbols formula

Yvonne "Buffy" Frongia

Hi, I am a new user and I am trying to design a sheet for us. I am trying to write a formula to have the symbols return the following: If % complete = 100%, return gray; If target day is today and it is less than 100% complete, return Red; If the % complete is less than 75% and the target day is within 3 days of today, return yellow; If the status is not started or not near the target date, return green. I am able to get the red, yellow and gray symbols to work, but I cannot get the green one to work. I tried making it less complicated and still no luck. Here are some examples of what I was inputting:

=IF([% Complete]@row = 1, "Gray", IF(TODAY() > [Target Date]@row, "Red", IF(TODAY(3) >= [Target Date]@row, IF([% Complete]@row < .75, "Yellow", "Green"))))

I also tried:

=IF([% Complete]@row = 1, “Gray”, IF([Target Date]@row>TODAY(), “Red”, IF([Target Date]@row<TODAY(3),”Yellow”, IF(AND(% complete@row<1,target date@row<TODAY(),”Green”))))

and

=IF([% Complete]@row = 1, "Gray", IF([Target Date]@row > TODAY(), "Red", IF([Target Date]@row < TODAY(3), "Yellow", IF(AND([% complete@row]<1,[target date]@row<TODAY()<"Green"))))

I hope you can help!

Andrew Stewart

Hi Buffy,

I have not tested it, but I think the problem with the first one is a missing AND. The way it is written, you can only get to green if TODAY(3) >= [Target Date]@row. Try instead:

=IF([% Complete]@row = 1, "Gray", IF(TODAY() > [Target Date]@row, "Red", IF(AND(TODAY(3) >= [Target Date]@row, [% Complete]@row < .75), "Yellow", "Green")))

Regards,

Andrew