Formula for RYG using % Complete and Finish Date
I am trying to populate my Status field, using RYG formatting (based off the % Complete and Finish Date). I am trying to use an AND/OR and it is not working for me. I can get half way there, but not completely.
My Status Definitions:
Gray  Not Started
Yellow  In Progress
Green  Completed
Red  At Risk
I would like to get the formula to say:
IF the % Complete is 100% then Status is Green (regardless of date)
IF % Complete is between 199% AND Finish Date is prior to 3 Days before it is due, then Status is Yellow
IF % Complete is between 199% AND/OR Finish Date is within 3 Days or Past due then Red
IF % Complete is 0% OR Finish Date is Blank then Gray
I seem to be able to get this to work only based on the Finish Date or based on the % Complete, but I am having trouble getting it all. Any help would be greatly appreciated! Thank you!
Comments

=IF([% Complete]1 = 1, "Green", IF(AND([% Complete]1 < 1, [% Complete]1 > 0), IF(NETDAYS([Finish Date]1, TODAY()) <= 3, "Yellow", "Red"), "Gray"))

I will try that. Thanks!

Unfortunately I got an UNPARSEABLE.

Got it to work!! Thank you!

Not a problem. For future reference smartsheet has a pretty good resource for this type of problem.
https://www.smartsheet.com/blog/supporttipautomateRYGballs

HI  OK after further review I am 90% there, I think. I have combed through the Community and the RYG Tips. I have tweaked the formula based on what I could find, but still something is off that I am not able to pinpoint it. I have 2 formulas I am trying.
IF % Complete is 100% Then Green
IF % Complete is between 199% and [Finish] date is 3 days out, then Yellow
IF [Finish] date is less than 3 days out (or past due) then Red
Otherwise Gray
Formula1:
=IF([% Complete]15 = 1, "Green", IF(AND([% Complete]15 < 1, [% Complete]15 > 0), IF(NETDAYS(Finish15, TODAY()) <= 3, "Yellow", "Red"), "Gray"))
This works to turn Green and Yellow, but if date is past due then it does not change to Red and will also not change from Gray to Red if Date is past due.
Formula2:
=IF([% Complete]20 = 1, "Green", IF(AND([% Complete]20 < 1, [% Complete]20 > 0), IF(TODAY()  Finish20 < 3, "Yellow"), IF(TODAY()  Finish20 < 3, "Red", "Gray")))
This works to turn Green and Yellow, but if date is past due then it does not change to Red and will also not change from Gray to Red if Date is past due.
I know in both of these it is an issue with the [Finish] date but where am I going wrong?
Thanks,
Amy

The main issue is that <= 3, "Yellow"
All negative numbers are less than a positive one, and thus it is always popping true. This means you need another if statement.
if( < 0,"red","yellow") where "Yellow" currently is in the first formula
Untested*
=IF([% Complete]15 = 1, "Green", IF(AND([% Complete]15 < 1, [% Complete]15 > 0), IF(NETDAYS(Finish15, TODAY()) <= 3, if(netdays(Finish15,today())<0,"Red","Yellow"), "Red"), "Gray"))
Help Article Resources
Categories
Check out the Formula Handbook template!