Formula Needed
Hi Smartsheet Team,
I am looking for help with a formula:
I have a column "Schedule Health" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns "Status", "Start Date", "End Date".
Ball is GREEN if "Start Date" is 7 days from today (ie project has not started yet)
Ball is GREEN if "Status" is "Complete"
Ball is YELLOW if "Start Date" is 3 days from today
Ball is YELLOW if "Status" is "In Progress" and "Start Date" is in the future
Ball is RED if "Status" is "In Progress" and "Start Date" is today or past (Project running late)
Ball is RED if "Status" is "In Progress" and "End Date" is today or later (Project started but not completed on time)
Ball is RED if "Status" is anything other than "Complete" at "End Date" for today or late (No body has updated the project and its late to complete)
Can anyone help me please?
Best Answer
-
Hey @Rod Sanderson ,
=IF(OR(AND(Status@row <> "Complete", [End Date]@row <= TODAY()), AND(Status@row = "in progress", [End Date]@row >= TODAY()), AND(Status@row = "in progress", [Start Date]@row <= TODAY())), "Red", 0)
I think this should work for the red.
Let me know if this is the direction you are looking for and I can write the rest of the colors as well.
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Answers
-
Hey @Rod Sanderson ,
=IF(OR(AND(Status@row <> "Complete", [End Date]@row <= TODAY()), AND(Status@row = "in progress", [End Date]@row >= TODAY()), AND(Status@row = "in progress", [Start Date]@row <= TODAY())), "Red", 0)
I think this should work for the red.
Let me know if this is the direction you are looking for and I can write the rest of the colors as well.
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Wow such a quick response - that looks like exactly what I am looking for - if you could do it for the other colors I will drop it into sheet and run it! THANK YOU!
-
I placed this in my Smartsheet and it works for some of Red except it does not change to Yellow if the status in 'In progress" and between the "Start Date" and "End Date" - Also if the project is "In progress" before the "Start Date" it also goes Red, and will you be able to build for Yellow and Green too please.
-
Hi Itai did you see my response. It appears that the RED is not reacting appropriately and I was hoping you could help me to build the formula for the Yellow and Green?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!