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!

Find more posts tagged with

Formulas

Project Management

Comments

L_123

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

Amy Burns

I will try that. Thanks!

Amy Burns

Unfortunately I got an UNPARSEABLE.

Amy Burns

Got it to work!! Thank you!

L_123

Not a problem. For future reference smartsheet has a pretty good resource for this type of problem.

https://www.smartsheet.com/blog/support-tip-automate-RYG-balls

Amy Burns

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 1-99% 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

L_123

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"))

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
Sheet Summary Formula Help
Hello, I am trying to get this formula to work but am not having any luck. When I remove the last @cell….text below the formula works great. When I add it in I get an error. Any ideas? =COUNTIFS([ACCT READINESS STATUS PLV]:[ACCT READINESS STATUS PLV], OR(@cell = "PENDING LOCAL CAH", @cell = "ADDITIONAL REVIEW NEEDED LOCAL…
How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…