Overdue formula and health status
Hi there,
I need a little help please, I am trying to get two formulas for the following:
Formula 1
I need my status of a task to reflect the proper status based on the percentage completed and the due date of the tasks. My status options are:
Not started (this is for something that is on 0%, but isnt past the due date)
Not started - Overdue (this is for something that is on 0% and is past the due date)
In Progress - On Track (this is for something that is not at 0% and isnt past the due date)
In Progress - Overdue (this is for something that is not at 0% and is past the due date)
Completed (Percentage complete is 100%)
Formula 2
Then once the formula above is working. I then want the health ball to reflect the status of the task, colours I would need are below:
Not started - Grey
Not started - Overdue - Red
In Progress - On Track - Yellow
In Progress - Overdue - Red
Completed - Green
Please help me!
Answers
-
Hi @clairemorse09,
For Formula 1:
=IF([% complete]@row = 1, "Completed", IF(AND([% complete]@row = 0, [Due Date]@row > TODAY()), "Not started", IF(AND([% complete]@row = 0, [Due Date]@row < TODAY()), "Not Started - Overdue", IF(AND([% complete]@row > 0, [Due Date]@row > TODAY()), "In Progress - On Track", IF(AND([% complete]@row > 0, [Due Date]@row < TODAY()), "In Progress - Overdue")))))
For Formula 2:
=IF([Formula 1]@row = "Not Started", "Gray", IF(OR([Formula 1]@row = "Not Started - Overdue", [Formula 1]@row = "In Progress - Overdue"), "Red", IF([Formula 1]@row = "In Progress - On Track", "Yellow", IF([Formula 1]@row = "Completed", "Green"))))
Sample:
Hope this helps, but if you've any problems/questions then just post! 🙂
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives