Trouble with a four color symbols formula

Ashley McAdoo

Hello everyone,

If am trying to indicate project color status based on <TODAY() and TODAY(+x) for green, yellow, red and blue symbols. This is my formula-=IF(OR([Requested Go Live Date/Discontinue Service Date]@row]<=TODAY(+5) "Red", (OR([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+9) "Yellow",(OR([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+14))))) "Green", "Blue")

I can get the Green and Red working, but not the Yellow or Blue, so obviously something is wrong with my formula. Any thoughts?

I also have 2 Status and 2 Due Date columns based on the need for a due date faster than the standard turnaround time; "Requested Go Live Date/Discontinue Service Date" and "Expected Completion Date". There is a function that automatically fills in the "Expected Completion Date" with Date of Request +14 or 28, as appropriate, and I am using conditional formatting to turn the text white for the dates in the "Expected Completion Date" column when there is a date in the "Requested Go Live Date/Discontinue Service Date" column. What I can't turn white are the colored status symbol dots in the "Status Requested Go Live Date/Discontinue Service" and "Status Expected Completion Date" columns when they are not needed. Any suggestions on how to handle that?

Thanks so much,

Ashley

Grid with formula problem.PNG

Andrée Starå

Hi @Ashley McAdoo

I hope you're well and safe!

Try something like this. (You probably would need to change the order)

=IF([July 2022]@row = "", "", IF([July 2022]@row > 0.995, "Green", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row < 0.98, "Red", "Gray"))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Ashley McAdoo It looks like that second one should work if you get rid of the OR function and its set of opening and closing parenthesis.

Paul Newcome

Ok. Exactly which scenario isn't working?

Maybe this?

=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", "Green")), "Gray")

Paul Newcome

Lets try fixing some syntax errors to see where we can get from there...

=IF([Requested Go Live Date/Discontinue Service Date]@row]<=TODAY(+5), "Red", IF([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+9), "Yellow", IF([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+14), "Green", "Blue")))

Ashley McAdoo

@Paul Newcome,

Thank you. That fixed one problem after I took out the extra bracket after the first @row. I still need to trigger the blue color based on whether or not a completion date has been populated, but this is a start. Any idea how to make the color dots not show when they are not needed? (i.e., the ones in the Expected Completion date column when the Requested Go Live date is what we are basing completion on.)

Thank so much,

Ashley

Paul Newcome

You would need to include another IF statement at the beginning to output blank (a set of quotes with nothing in between) when you don't want a color populated.

Blank = ""

Ashley McAdoo

Hey @Paul Newcome,

I am stuck on this again. I am trying to populate Red, Green or Gray (Gray is no data) on some rows where there are no parameters for Yellow, and R,GN, Y or GY on other rows where there are parameters for yellow. I am trying to follow the formula you gave me above, but I can I only get R or GN to populate, nothing else. Attached is the data I am working with and here are my 2 formulas. What am I doing wrong?

=IF(OR([July 2022]@row>= 0.95, "Green", [July 2022]@row ,= 0.95"Red", "Gray")) UNPARSEABLE

=IF(OR([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray")))) INCORRECT ARGUEMENT SET

Thank you,

Ashley

Formula Errors.PNG

Andrée Starå

Hi @Ashley McAdoo

I hope you're well and safe!

Try something like this. (You probably would need to change the order)

=IF([July 2022]@row = "", "", IF([July 2022]@row > 0.995, "Green", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row < 0.98, "Red", "Gray"))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Ashley McAdoo It looks like that second one should work if you get rid of the OR function and its set of opening and closing parenthesis.

Ashley McAdoo

@Andrée Starå , thanks so much. That is working!

Actually I spoke too soon. I realized it is populating gray only, regardless of the number in the other column.

Ashley McAdoo

@Paul Newcome, that halfway worked. It is filling in colors, but it's filling in red for cells where there is no data.

Paul Newcome

Because blank is less than 0.98. Try starting it off like this:

=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))

Ashley McAdoo

Hey @Paul Newcome

Finally got to get back on this project. Gray is populating where it should be Yellow. I wanted to Gray to populate for blank spaces, but now I need it to populate for N/A. Green and Red seem to be okay, but there is no yellow at all. I tried to tweak the formula but did not have any luck. Suggestions?

Thanks,

Ashley

Paul Newcome

I think there may be some confusion with your criteria. Your existing formula says...

Less than .98 = Red

Less than or equal to negative .03 = Yellow

Greater than .995 = Green

Are you able to clarify?

=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))

Ashley McAdoo

Yellow is supposed to be a range between 0.995 and 0.98, not an equation.

Paul Newcome

@Ashley McAdoo Try this then:

=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))

Ashley McAdoo

Hey @Paul Newcome,

The formula still doesn't work. If there is no data in the July 2022 cell than the symbol should be gray. If July 2022 is less than 98% it should be red. If July 2022 is greater than 99.5% it should be green. Anything that falls between 98 and 99.5 percent should be yellow. I hope this makes it more clear.

Thanks,

Ashley

Paul Newcome

Ok. Exactly which scenario isn't working?

Maybe this?

=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", "Green")), "Gray")