Formula Not Operating Properly
Hello, I am testing out the Construction/Facilities Management Template Set and I think the formula is incorrect in the sheet for the Schedule Health.
=IF(State3 = "Complete", "Green", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red", IF(AND(State3 = "Not Started"), "Blue", "Yellow")))
The way it is currently operating is if the status changes to complete it turns green (good), however any other status; in progress, not started, the button turns red. Can someone advise on how the formula should read?
Ideally I think we would want red when due date is past due, blue when not started, yellow in progress.
Thank you
Best Answer
-
You're going to have to maneuver the formulas such that red is the last option since the qualifications for it will be satisfied by things you want other colors to be used for.
I included an example of this below. If you have a status for "State" that contains "In Progress", you can use that to add to the formula to get a more exact end result.
=IF(State3 = "Complete", "Green", IF(AND(State3 = "Not Started"), "Blue", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red", "Yellow")))
Answers
-
You're going to have to maneuver the formulas such that red is the last option since the qualifications for it will be satisfied by things you want other colors to be used for.
I included an example of this below. If you have a status for "State" that contains "In Progress", you can use that to add to the formula to get a more exact end result.
=IF(State3 = "Complete", "Green", IF(AND(State3 = "Not Started"), "Blue", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red", "Yellow")))
-
Thank you! This has been driving me crazy!
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!