RYB Automation Question
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.
Best Answers
-
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")))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
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.
-
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.
Answers
-
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")))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
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")))
-
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")))
-
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")))
-
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.
=IF([SmartXpo Launch Date]32 >= TODAY(10), "Red", IF([SmartXpo Launch Date]32 >= TODAY(90), "Yellow", IF([SmartXpo Launch Date]32 >= TODAY(100), "Green")))
-
Which invalid are you getting?
Column Value
Data Type
Operation
Ref
Value
-
Invalid Operation
-
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.
-
That fixed that one, thank you for all your help Paul! Have a good one.
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
