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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!