Automating RGYB Using Dates and % Complete
I am currently creating a project template that I wanted to add some automation into. The first piece of that automation is to change the RGYB using the finish date and % complete. The formula that I currently have works great when you have dates in the cells, but that won't always is the case when a task is being added.
What am I missing to have the ball green when there is no date?
=IF([% Complete]4 = 1, "Blue", IF(AND(Finish4 < TODAY(), [% Complete]4 < 1), "Red", IF(AND(Finish4 = TODAY(), [% Complete]4 < 1), "Yellow", IF(AND(Finish4 > TODAY(), [% Complete]4 < 1), "Green", "Blue"))))
Thanks,
Dan
Comments
-
Give this a try.
=IF(NOT(ISDATE(Finish@row)), "Green", IF([% Complete]4 = 1, "Blue", IF(AND(Finish4 < TODAY(), [% Complete]4 < 1), "Red", IF(AND(Finish4 = TODAY(), [% Complete]4 < 1), "Yellow", IF(AND(Finish4 > TODAY(), [% Complete]4 < 1), "Green", "Blue")))))
-
That solved my problem. I appreciate the help.
-
Awesome. I'm so glad I could help you out!
-
Hi Daniel,
To add to Mike's excellent advice.
You can use @row in the formula instead of the row number if the formula is on the same row. Then you won't need to think about row numbers, and it's more efficient and takes less processing power.
Here's the formula with @row instead.
=IF(NOT(ISDATE(Finish@row)), "Green", IF([% Complete]@row = 1, "Blue", IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row = TODAY(), [% Complete]@row < 1), "Yellow", IF(AND(Finish@row > TODAY(), [% Complete]@row < 1), "Green", "Blue")))))
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives