Project Health Formula to Auto Populate a Symbol

Options

Hi

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.

Steve

Smartsheet Health Formula 1.jpg


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!