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 1-99% AND Finish Date is prior to 3 Days before it is due, then Status is Yellow
IF % Complete is between 1-99% 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!
Help Article Resources
Check out the Formula Handbook template!