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!