Project Health Formula
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.
Answers
-
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"))))))
Megan Ryzenga | Medical Education Analyst
3M Health Care, Medical Solutions Division
She/Her
-
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.
-
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"))
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!