Show Red Ball if Past Due
I have a formula in the "Health" column of a sheet. This formula shows a colored ball depending on the "Status" of another column. The "Status" column is determined by another column, "% Complete". Ultimately, the part of these formulas not working is that I want it to be a Red Ball if the End Date is past and it has not been marked Complete.
=IF([% Complete]@row <= 0.01, "Not Started", IF(AND([% Complete]@row > 0.01, [% Complete]@row < 0.99), "In Progress", IF([% Complete]@row >= 0.99, "Complete", "")))
=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1, "Red")))))
In the above pic, Can somebody explain what is wrong with these formulas and make suggestions to fix? All help is appreciated. Thanks.
Help Article Resources
Check out the Formula Handbook template!