Formula isn't working properly
I created the below formula to calculate project schedule health and cannot seem to have it return expected outcomes. Can someone help me get this right?
Red
- If a task is not completed and the target date is either today or in the past; OR
- if the end date (end date is captured when %Compete is 100%) is 5 days after the target end date (I'd like this to be 5 net work days if possible)
Yellow (this one is a bit more involved)
- if % Complete is less than than (count of days since start date)/(duration) difference of start date and today (the formula has a *-1 and a -1 to not count the first day and keep responses positive); OR
- if end date is within 5 days of target end date
Green
- default to green if blank
- if the above conditions aren't found should be green
- if completed on or before target end date
=IF(OR(AND(Status@row <> "Complete", [Target End Date]@row <= TODAY()), [End Date]@row > [Target End Date]@row + 5), "Red", IF(OR([% Complete]@row < ((((NETWORKDAYS(TODAY(), [Start Date]@row)) * -1) - 1) / Duration@row), [End Date]@row > [Target End Date]@row + 5), "Yellow", "Green"))
Edited to bold logic that isn't working properly and added third bullet to Green
Answers
-
If you want it to be yellow if it is within 5 days after the target date you need to change it to < than instead of >
[End Date]@row < [Target End Date]@row + 5),"Yellow"
Will also need to add criteria for [End Date]@row>[Target End Date]@row so it won't mark it as yellow until the Target End Date has passed.
If you are wanting it to be yellow within 5 days before the Target end Date then you would just need to change the + 5 to a -5
Help Article Resources
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
Check out the Formula Handbook template!