Project Health Formula to Auto Populate a Symbol


I have a formula that flags the health of each row (task) via a RAG status. Green is healthy, Amber is at risk or in progress and Red is a failure.

This is set up to work off the start date and % complete. However, it just isn't right.

The formula is;

=IF(OR([% Complete]@row = 1, Start@row > TODAY(7)), "Green", IF(OR(Start@row <= TODAY(), Start@row = TODAY(1)), "Red", IF(OR([% Complete]@row < 1, Start@row >= TODAY(7)), "Yellow")))

The issue I am having is that it is showing red if the task has started and % complete is greater than 0%. What I want it to display is the following

Green - 100% complete or not due to start for 7 days

Amber - If less than 7 days to start or if beyond irrelevant of % complete or Start date is in the past and % complete is greater than 0%.

Red - If The start date is today or in the past and the % is 0

From this I run a report over various projects so I can see what is at risk. At the moment with the current formula it is showing the project is at risk even though it has started and the % complete is greater than 0.

Thanks in advance.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!