# Project Status Health Formula (Using Due Date, Today's Date, and % Complete)

✭✭

Hello,

I am new to SmartSheet, and I'm trying to create a formula that shows project health status using the color symbols. These are my parameters below. I've not been able to get any formula to work. I'd greatly appreciate any help with creating this formula.

• PAST DUE - Red: Today’s Date is 1 day after Due Date AND % Complete is < 100%
• AT RISK - Yellow: Today’s Date is 10 before Due Date AND % Complete is < 75%
• ON TRACK - Green: Today’s Date is 10 before Due Date AND % Complete is > 75%
• COMPLETE - Blue: % Complete is 100% regardless of the date.

Thanks!

NC

• ✭✭✭✭✭✭

Try something like this...

=IF([% Complete]@row = 1, "Blue", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(10) >= [Due Date]@row, IF([% Complete]@row < .75, "Yellow", "Green"))))

• Just curious if you were able to get this to work. I'd like to do this too.

• Employee

I responded on your other post (here), but this post has different criteria than that one.

Would you be able to clarify what you're looking to do? If you can explain when you want each status ball colour to appear, we can help with the Nested If statement to get you there. It may also be helpful to see a screen capture of your sheet (but block out any sensitive data).

Thanks!

Genevieve