RYG Column based on % Complete and Date
Answers
-
I'm trying to have my RYG column automated based on % Complete and Due Date.
If Due Date is in the future, Green
If Due Date is in the past and % complete = 100%, Green
If Due date is in the past and % Complete is less than 100%, Red
Then to create yellow, I want to look at % complete vs duration and refer to due date. So if the duration is 10 days and the due date is fewer than 5 days away and % complete is less than 50% - I want to return yellow.
How can I phrase this formula?
-
Hi @Robert Cordtz ,
I'll break down each instruction by colour... then we'll order them together:
If Due Date is in the future, Green
or
If Due Date is in the past and % complete = 100%, Green
IF(OR([Due Date]@row > TODAY(), AND([Due Date]@row <= TODAY(), [% Complete]@row = 1)), "Green",
If Due date is in the past and % Complete is less than 100%, Red
IF(AND([Due Date]@row <= TODAY(), [% Complete]@row < 1), "Red",
So if the duration is 10 days and the due date is fewer than 5 days away and % complete is less than 50% - I want to return yellow.
IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 0.5, Duration@row = 10), "Yellow"
Now that you have 3 rules, lets make sure the put them in the right order. IF statements read left-to-right, and as soon as a criteria is met it will stop. This means that if we started with the Green rule, it wouldn't take in to account that the Yellow rule also looks into the future a bit. So! Let's order them backwards, starting with the Yellow rule, moving to the red, and finishing with the green.
FULL FORMULA:
=IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 0.5, Duration@row = 10), "Yellow", IF(AND([Due Date]@row <= TODAY(), [% Complete]@row < 1), "Red", IF(OR([Due Date]@row > TODAY(), AND([Due Date]@row <= TODAY(), [% Complete]@row = 1)), "Green")))
Let me know if this works for you! Keep in mind there may be gaps in there that we haven't specified a certain colour for, so test it out on all the possible date options you see that might happen to make sure it's outputting the correct colour.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
- 145 Just for fun
- 62 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!