How can I get the status to change to "On Schedule" when the Finish date is within a week?
I am needing help with a formula to generate a "On Schedule" status that will in turn turn my RGB balls green instead of showing up "At Risk" or red. Perhaps I'm confusing the referencing by my Health column formula ??? Any help is appreciated. Thanks!
My Health Column formula is:
=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "On Schedule", "Green", IF(AND(Status@row <> "Complete", TODAY() > Finish@row), "Red"))))
My Status Column formula is:
=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress", IF(Finish@row = WEEKNUMBER(TODAY(), "On Schedule")))))
Answers
-
Nest IF statements work from left to right and stop on the first true value. That means that your % Complete arguments are going to trigger a true value before it has a change to evaluate the date. Try moving that particular IF to be the second on (after the "Complete" IF).
-
Ok, I gave that a try and now it is indicating I have a "Circular Reference". I also tried making it the first command and it still said "Circular Reference".
=IF([% Complete]@row = 1, "Complete", IF(AND(Start@row < TODAY() - 5, Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))
=IF(AND(Start@row < TODAY() - 5, Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))
-
If you are putting this in the Status column, then you need to remove the reference to the Status column.
=IF([% Complete]@row = 1, "Complete", IF(AND(Start@row < TODAY() - 5,
Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))=IF([% Complete]@row = 1, "Complete", IF(Start@row < TODAY() - 5, "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))
-
THANK YOU! THANK YOU!
This exact thing didn't work but the combination of everything you suggested did.
This is the formula I got to work using both suggestions:
=IF(AND([% Complete]@row = 0, Start@row < TODAY() - 5), "On Schedule", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))
You are greatly appreciated! Thank you for your time! :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!