RYB Automation Question

Manuel Lemus

Hi,

I am looking for some assistance with automating the RYG Status Balls in my Sheet. Below is my data, and I am attempting to key off the "smartxpo launch date". What I want to do is assign a Red, Green, or Yellow status ball with the conditions below. IF the SmartXpo Launch Date is between 100-90 days, then assign a green ball. If the date is between 90-10 days then assign yellow ball. If the date is between 10-0 days, then assign a red ball. Note I added a column to calculate the days out, but I cannot figure out how to add the formula.

Frank Falco

Try this

=IF([Reebok Planning Days Out]@row<=10,"Red",IF([Reebok Planning Days Out]@row<=90,"Yellow",IF([Reebok Planning Days Out]@row>90,"Green")))

if you do not want to include negative days it would be

=IF(AND([Reebok Planning Days Out]@row<=10,[Reebok Planning Days Out]@row>=0),"Red",IF([Reebok Planning Days Out]@row<=90,"Yellow",IF([Reebok Planning Days Out]@row>90,"Green")))

Paul Newcome

Ok. Go into the column properties of your [SmartXpo Launch Date] column and double check that it is in fact set to be a date type column.

Paul Newcome

Happy to help! 👍️

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

Frank Falco

Try this

=IF([Reebok Planning Days Out]@row<=10,"Red",IF([Reebok Planning Days Out]@row<=90,"Yellow",IF([Reebok Planning Days Out]@row>90,"Green")))

if you do not want to include negative days it would be

=IF(AND([Reebok Planning Days Out]@row<=10,[Reebok Planning Days Out]@row>=0),"Red",IF([Reebok Planning Days Out]@row<=90,"Yellow",IF([Reebok Planning Days Out]@row>90,"Green")))

Paul Newcome

You can actually not worry with the extra column if you don't want to.

=IF([Date Column]@row <= TODAY(10), "Red", IF([Date Column]@row <= TODAY(90), "Yellow", IF([Date Column]@row <= TODAY(100), "Green")))

Manuel Lemus

Thank you! I tried this, but I am getting an Unparsable Error. Do you know why? Here is my formula: =IF([Rebook Meeting Date]32 <= TODAY(10), "Red", IF([Rebook Meeting Date]32 <= TODAY(90), "Yellow", IF ([Rebook Meeting Date]32 <= TODAY(100), "Green")))

Paul Newcome

You have a space after your 3rd IF. Here it is removed...

=IF([Rebook Meeting Date]32 <= TODAY(10), "Red", IF([Rebook Meeting Date]32 <= TODAY(90), "Yellow", IF([Rebook Meeting Date]32 <= TODAY(100), "Green")))

Manuel Lemus

Hi Paul,

I really appreciate your help. Maybe I am doing something wrong because I took the formula and remove the space after the 3rd IF but I am now getting an invalid result.

2020-03-10_9-55-40.jpg

=IF([SmartXpo Launch Date]32 >= TODAY(10), "Red", IF([SmartXpo Launch Date]32 >= TODAY(90), "Yellow", IF([SmartXpo Launch Date]32 >= TODAY(100), "Green")))

Paul Newcome

Which invalid are you getting?

Column Value

Data Type

Operation

Ref

Value

Manuel Lemus

Invalid Operation

Paul Newcome

Ok. Go into the column properties of your [SmartXpo Launch Date] column and double check that it is in fact set to be a date type column.

Manuel Lemus

That fixed that one, thank you for all your help Paul! Have a good one.

Paul Newcome

Happy to help! 👍️

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