Help with formula with RYG Balls and Start/End Dates

Michelle Rojas

Hello,

I am trying to clean up a program management smartsheet. I am wanting the status column (RYG balls) to communicate with the % complete column and the start and end date column.

This is my current formula:

=IF([% Complete]93 = 1, "Green", IF([% Complete]93 > 0.49, "Yellow", IF([% Complete]93 < 0.48, "Red")))

With this formula, I am running into issues. When a task has a start date in the future, the status ball is red. I am wanting to eliminate this issue because the task cannot be completed until the future start date. Also, some tasks have end dates that are past due and the RYG balls do not change red because they are talking to the %complete. Ex: Task A is 75% complete but 2 weeks past due = Yellow Circle.

Is it possible to have the formula do these things I am asking for? I may have to choose to only focus on % complete or start/end dates.

Paul Newcome

You can account for all of these things within the same formula. What are your exact criteria for each color? Once we get that part figured out, we can start working on building the formula.

Michelle Rojas

Red is late/not complete (less then 49%)

Yellow is pending/half way complete

Green is done/complete 100%

I hope that is helpful

Paul Newcome

A few questions...

"Red is late/not complete (less then 49%)"

Do you mean late OR less than 49%? What if it is less than 49% but not late? What if the Start Date is in the past, the End Date is in the future, but the % is 0? Does the Start Date being in the past and the task not being started yet count as "late"?

"Yellow is pending/half way complete"

What is "pending"? By "halfway" do you mean exactly 50% or do you mean 49% and greater?

"Green is done/complete 100%"

Is "done" determined by a checkbox? Is it date driven, or is the only way for it to be "Done" is when complete is 100%.

As much detail into every requirement as possible would really help. Here is what I suggest:

Create a list with each of the colors listed.

Create a list of every possible outcome but keep in mind that if the dates are the same and 1% is treated the same as 10%, then you can just say "1% - 10%". There's no need to duplicate it for 1%, 2%, 3%, 4%, etc if they are all the same result.

Then take each of those outcomes (as many different possibilities that you will want to differentiate between) and move them to under each of the colors.

Something like this...

Red:

% Complete is less than 100 AND Finish Date is in the past

% Complete is less than 50 AND Start date is in the past (even if Finish Date is in the future)

% Complete is 0 and Start Date is in the past

Yellow:

% Complete is greater than or equal to 50 and Finish Date is in the Future

% Complete is less than 100 and Finish Date is TODAY

Green:

% Complete is 100

Of course if there are other possible combinations that need to be accounted for, you would put them in place. Once we get all of the detailed logic figured out we can start building out a formula for testing.

Michelle Rojas

Great comments/questions! I like the ones you have written so I have just copied it below. But I do have an additional requirement: Is there any option for no color ball for items with a future start date and future end date? I would like there not to be a colored ball if the tasks is not being worked on at all because it cant be until a future date.

Red:

% Complete is less than 100 AND Finish Date is in the past

% Complete is less than 50 AND Start date is in the past (even if Finish Date is in the future)

% Complete is 0 and Start Date is in the past

Yellow:

% Complete is greater than or equal to 50 and Finish Date is in the Future

% Complete is less than 100 and Finish Date is TODAY

Green:

% Complete is 100

Paul Newcome

It is very possible. So lets break this down in formulas...

Blank:

IF([Start Date]@row > TODAY(), "")

Green:

IF([% Complete]@row = 1, "Green")

Red:

IF([Finish Date]@row < TODAY(), "Red") <------- We do not need to specify that it is less that 100% because to get to this point in the formula it would have to be. Nested IF's read left to right and stops at the first "true" value. So if it get's to this point, then it has already determined that it is less than 100%.

IF([% Complete]@row < .5, "Red") <------- Same thing as above. By getting to this point in the formula, we already know that the Start Date must either be today or in the past because if it was in the future, it would have stopped at the first "true" which generates a blank.

IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Red")

which can be combined using an OR for each of the criteria to include nesting the AND inside of the OR...

IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red")

Yellow:

IF([Finish Date]@row = TODAY(), "Yellow") <------- Same as the second "Red". We already established that it would have to be less than 100% to make it this far into the formula.

IF([% Complete]@row >= .5, "Yellow")

which can be combined using an OR for each of the criteria

IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow")

.

Now we can put it all together...

=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow"))))

You can also combine the Blank, Green, and Red outputs as above and just tell the formula to generate "Yellow" if all of the previous fail. That would be entirely up to you.

=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", "Yellow")))

For testing purposes, you could go with the first but include something along the lines of "Scenario Not Accounted For" if all of them fail including the yellow. This way as you test every possible scenario you can think of, you won't generate a false blank or yellow. Once you figure out that everything is covered, you can switch back to one of the first two or even leave it as is so that if something comes up that you didn't think of in testing, you can be alerted.

=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow", "Scenario Not Accounted For"))))

Michelle Rojas

These formulas are not working for my smartsheet. Any other ideas/suggestions?

Paul Newcome

What do you mean by "not working"? Are you getting an error? Incorrect results? Unexpected behavior?

Michelle Rojas

#UNPARSEABLE is the message I am receiving from all of the formulas you recommended.

Paul Newcome

Have you double checked that the column names are correct?

Michelle Rojas

It was user error. I accidentally capitalized one letter which through everything off until I fixed it. This works great! Exactly what I was hoping for.

Thank you

Paul Newcome

That'll do it. Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that one may be found here.