Need help with Health formula
Created a project plan with hierarchies.
Created a column called LEVEL which gets a 1 if line item is a task ..this is working fine and I'm able to create reports.
Created a column called HEALTH which gets a red/yellow/green ball depending on whether the task is overdue (red), on target (green) or some risk (yellow).
The formula for RED works fine by itself:
=IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red")
The formula for GREEN works fine by itself:
=IF(AND([Status]@row = "Complete", Level@row = "1"), "Green")
I can't get both to work at the same time, and need help with creating formula for YELLOW.
Thank you.
Best Answer

This should remove the status symbols from any row where Level does not equal 1 without affecting the rest of the rows.
=IF(Level@row <> 1, "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red", IF(AND([End Date]@row < TODAY(2), Status@row <> "Complete", Level@row = "1"), "Yellow", "Green")))
Cheers,
John
Answers

How are you assessing risk to determine if the cell should be green or yellow? Is it based off of a number of days left until due date or some other criteria? A nested if formula should work in this case, but need to know you calculate the risk level.
Cheers,
John

Yes, apologies for not being clear ... YELLOW will be based on 1 day left until due date.

The formula below should handle what you are looking to do and can be converted to a column formula. It should show red for overdue, yellow for due today or tomorrow, and green for anytime in the future.
=IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red", IF(AND([End Date]@row < TODAY(2), Status@row <> "Complete", Level@row = "1"), "Yellow", "Green"))
Cheers,
John

This is working great!! I need just the actual tasks to have a RED/YELLOW/GREEN dot and not the parent  so if it has a 1 in the Level column. All columns have a dot now.


This should remove the status symbols from any row where Level does not equal 1 without affecting the rest of the rows.
=IF(Level@row <> 1, "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red", IF(AND([End Date]@row < TODAY(2), Status@row <> "Complete", Level@row = "1"), "Yellow", "Green")))
Cheers,
John

This did the trick!! Thank you so much!!

You're welcome.
Help Article Resources
Categories
Check out the Formula Handbook template!