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

NMC AH · 01/02/20

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

Paul Newcome · 01/02/20

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

Candace Hebert · 06/15/20

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

Genevieve P. · 06/15/20

Hi @Candace Hebert

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

Nitija P · 06/03/22

https://community.smartsheet.com/discussion/comment/236066#Comment_236066

This worked for me. Just ensure to select correct column names.

Unknown · 08/18/23

This content has been removed.

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

Answers

Help Article Resources

Categories