Multiple IF / AND / OR Statements Help, Please.
This is such a great community and a wealth of knowledge for a new Smartsheet user like me. I have done several formulas, but this one has me scratching my head.
I have a [Target Date] column, [% Complete] column, and status column that I would like to automate with Red, Yellow, Green, and Gray status balls. The [% Complete] column is a drop-down with options: N/A, 0%, 25%, 50%, 75%, and 100%.
I would like to have no status ball for "N/A". That seems to be the easy part.
Next, I would like the status ball to be Gray if the [% Complete] is 0% and the target date is 5+ days out and Green if the [% Complete] is 25%, 50%, or 75% and 5+ days out. I would like the status balls to change to Yellow if the [% Complete] is 0%, 25%, 50%, or 75% and 3 days or less from [Target Date], and have status balls change to Red if the [% Complete] is 0%, 25%, 50%, or 75% and the [Target Date] is the date before the target date, date of target date, or is past the target date.
I won't need a status ball for [% Complete] = 100%, as I plan to set conditional formatting to show a completed task.
I've tried several different variations today and am getting two different formula error messages. I thought I'd post this question, as there are several of you experienced users who seem to excel at these formulas.
Thank you in advance for your assistance!
Help Article Resources
Check out the Formula Handbook template!