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
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!