Nested If/And Formula Not working

Krystyna

Hello All,

I'm working on a project plan and want the status of the task to automatically appear as a color based on the following conditions:

If % Complete = 1 (100%), then Blue

If % Complete <1 and today => Finish (Date), Red

If % Complete <.50 and today - 5 days is equal to or less than Finish, then Red.

If % Complete <.75 and today - 5 days <= Finish, then Yellow, otherwise Green

Here's the formula I've tried without success:

If ([% Complete]@row=1, “Blue”, IF(AND([% Complete]@row<1,TODAY()=>Finish@row),”Red”, IF(AND([% Complete]@row<.50, TODAY([-5])<=Finish@row, “Red”, IF(AND([% Complete]@row<.75,TODAY([-5])<=Finish@row),”Yellow”,”Green”)))

Any help would be much appreciated.

Thanks,

Krystyna

Andrée Starå

Hi @Krystyna

I hope you're well and safe!

Try something like this.

=
If ([% Complete]@row=1, "Blue", 
IF(AND([% Complete]@row<1,TODAY()>=Finish@row),"Red", 
IF(AND([% Complete]@row<.50, TODAY([-5])<=Finish@row, "Red", 
IF(AND([% Complete]@row<.75,TODAY([-5])<=Finish@row),"Yellow","Green")))

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Krystyna

Hi Andree,

Thanks very much; unfortunately, it came back as unparseable.

Hope you're well and have a great New Year!

Krystyna

Andrée Starå

@Krystyna

Happy to help!

Yes, there seem to be multiple errors, but I thought I'd noticed them all.

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

Krystyna

@Andrée Starå I've sent an email with screenshot. Unfortunately, I'm not able to share the sheet outside my organization. Greatly appreciate you taking your time out to help me.