Need help with Health formula

MC100

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.

John Shane

@MC100

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

John Shane

@MC100,

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

MC100

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

John Shane

@MC100,

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

MC100

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.

MC100

John Shane

@MC100

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

MC100

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

John Shane

@MC100

You're welcome.