Calculation Help - Project Traffic Light RGY Based on Due Date, % Completed and Status
Hello I need a calculation assist.
Columns: Start, Due, Duration, % Completed, Status
Status drop downs:
Not Started,
In Progress,
Overdue,
Completed.
Need traffic light based on the following:
Complete 100% = Blue
Not Started 0% Complete
Due Date Over 7 Days = Green
Due Date Less than 5 Days = Yellow
Due Date Less than 2 Days = Red
In Progress 1%-99%
Due Date over 7 Days = Green
Due Date Less than 5 Days = Yellow
Due Date Less than 2 Days = Red
Overdue Any % = Red
Answers
-
Help please :)
-
Assuming the Due column is a date. In your symbol column you will need an IF function. I suggest you build this one part at a time and check it works, that will make it a lot easier to identify any missing commas or parenthesis, and will help to validate the logic.
Start with the blue:
=IF([% Complete]@row = 1, "Blue")
or
=IF(Status@row = "Completed", "Blue")
Either would work to turn the symbol blue, but I am going to use the Status column.
Then you can add another IF that is only evaluated if the first is false. Add the part in bold for red.
=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red"))
This will turn the symbol red if the status is not completed and the due date is less than or equal to two days from today.
Then another IF to be evaluated if both of those are false:
=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red", IF(Due@row <= TODAY(5), "Yellow")))
This will turn the symbol yellow if the status is not completed and the due date is not less than or equal to two days from today but is less than or equal to 5 days from today.
And you can probably guess what comes next. Another IF to be evaluated if the first 3 are false:
=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red", IF(Due@row <= TODAY(5), "Yellow", IF(Due@row > TODAY(5), "Green"))))
This will turn the symbol green if the status is not completed and the due date is not less than or equal to two days from today and is also not less than or equal to 5 days from today, but is more than 5 days from today.
-
THANK YOU!!!!!
-
Glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!