# Nested If/And Formula Not working

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

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Hi Andree,

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

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

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])

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• @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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!