Project Health Formula

Amanda Sligh

I am trying to get this formula to work on my project timeline. But keep getting #UNPARSEABLE.

=IF(OR(Status@row = "Not Started", [Target End Date]@row >= TODAY()), "Green","Yellow",IF(OR(Status@row = "In Progress", [Target End Date]@row >= TODAY()), "Green","Yellow",IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-15)), "Red", IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY(-15)), "Red",IF(Status@row = "Complete", "Gray"))))))

I would also like to have the overall health of the project rollup based on the tasks below not on the target end date.

Megan Ryzenga

Hi Amanda!

Try this:

=IF(Status@row = "Complete", "Gray",IF(OR(Status@row = "Not Started", [Target End Date]@row >= TODAY()), "Green",IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-15)), "Red",IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY(-15)), "Red",IF(OR(Status@row = "In Progress", [Target End Date]@row >= TODAY()),"Yellow"))))))

Amanda Sligh

I am not getting #UNPARSEABLE anymore but it's not calculating correctly. The rows that should be showing red are still showing green even though they are past due by more than 15 days.

Amanda Sligh

I don't think I included all the conditions before. These are the three statements I need to combine. They all work on their own just not together.

=IF(Status@row = "Complete", "Gray")

=IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-15)), "Red", IF(AND(Status@row = "In Progress", [Target End Date]@row >= TODAY()), "Green", "Yellow"))

=IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY(-15)), "Red", IF(OR(Status@row = "Not Started", [Target End Date]@row >= TODAY()), "Green", "Yellow"))

Cyndi

@Megan Ryzenga @Amanda Sligh - I have been searching for something like this - but I don't see there was ever a final solution and I'm having the SAME issue you were Amanda. I keep getting a green when I should a red. Just seeing if you ever were able to resolve this one.