RYG Formatting - Formula for Populating Status using %Complete and Finish Date
HELP!!! I am trying to populate my Status field, using RYG formatting (using the % Complete and Finish Date). The cell displays #UNPARSEABLE. I am not sure what I am doing wrong. I have been reading through the comments in the community and just cannot get this right.
I used this formula in Excel and it worked, but when I copy it into Smartsheet, it does not work at all:
=IFS(AND(I7<TODAY(),D7<1),"Red",AND(I7=TODAY(),D7<1),"Yellow",AND(I7>TODAY(),D7<1),"Green",D7=1,"Gray")
My Status Definitions:
Gray - Finished
Yellow – At Risk
Green – On Track
Red - Late
I would like to get the formula to calculate:
· If % Complete = 1 then Status is GRAY
· If Finish Date is less than Today AND % Complete is less than 1, then Status is RED
· If Finish Date = Today + the next 5 days and % Complete is less than 1, then Status is YELLOW (couldn’t figure out how to add the 5 days)
· If Finish Date is greater than Today + 5 days and % Complete is less than 1, then Status is GREEN (couldn’t figure out how to exclude the “yellow” days from this formula)
These are the two versions of the formula that I have tried. Neither one works:
=IF(AND([% Complete]7 <
1, Finish7 < TODAY()), "Red", IF(AND([% Complete]7 <
1, Finish7 < TODAY()), "Yellow", IF(AND([% Complete]7 <
1, Finish7 > TODAY()), "Green", IF([% Complete]7 >
1, "Gray,))))
=IF(AND(Finish7 < TODAY()), ([% Complete]7<1), "Red"), (AND(Finish7 = TODAY()), ([% Complete]7<1), "Yellow"), (AND (Finish7 > TODAY()), ISBLANK([% Complete]7), "Green"), IF(([% Complete]7 = 1), "Gray")
Thank you in advance for helping me. I cannot figure this out by myself.
Best Answer
-
Hi Lisa,
Since you want the status to be Gray if it's 100%, you can put this rule first in line, and then you don't need to specify the "less than 100%" for all the other criteria because the formula will only move to those if it's less than 100%.
Try this:
=IF([% Complete]@row = 1, "Gray", IF(Finish@row < TODAY(), "Red", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(5)), "Yellow", "Green")))
I didn't specify the Green rule because it will apply if none of the other criteria are met... namely, if the % is not 100% and the date is more than 5 days in the future.
Here are some articles that may help you:
Let me know if this works for you or if you have any questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Lisa,
Since you want the status to be Gray if it's 100%, you can put this rule first in line, and then you don't need to specify the "less than 100%" for all the other criteria because the formula will only move to those if it's less than 100%.
Try this:
=IF([% Complete]@row = 1, "Gray", IF(Finish@row < TODAY(), "Red", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(5)), "Yellow", "Green")))
I didn't specify the Green rule because it will apply if none of the other criteria are met... namely, if the % is not 100% and the date is more than 5 days in the future.
Here are some articles that may help you:
Let me know if this works for you or if you have any questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thank you so much for the explanation and formula!!! I have a better understanding of the logic and the formula you provided works exactly the way I wanted!
A million thanks!
--Lisa
-
Wonderful, I'm so glad to hear that! And happy to help.
-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
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!