# Creating Multiple If Statements

These formulas are hurting my head and I can't figure out why I keep getting errors on my stoplight formula - My column is set to symbols and I've selected red, yellow, green, blue

This is what I'm trying to do:

If % Complete = 100%, Blue

If % Complete < 100% AND the due date is more than 10 days away, Green

If % Complete < 100% AND the due date is in the next 5 days, yellow

If % Complete < 100 AND due date is less than today's date, Red

If % Complete = 0 , Black

Can someone help me create this formula.

• Overachievers

Hi @Malinda

Try this, where [Complete] is your % Complete column and [Due Date] is your date column.

=IF(Complete@row = 100, "Blue", IF(AND([Due Date]@row > TODAY() + 10, Complete@row < 100, Complete@row > 0), "Green", IF(AND([Due Date]@row < TODAY() + 5, [Due Date]@row > TODAY(), Complete@row < 100, Complete@row > 0), "Yellow", IF(AND([Due Date]@row < TODAY(), Complete@row < 100, Complete@row > 0), "Red", "Not started"))))

I've left your requirement for Black to read Not Started as I believe there are only 4 coloured symbols, so the ) complete will always be whatever text you add here.

Hope that helps

• ✭✭✭✭✭✭

Logically, this should give you the desired result:

=IF([% Complete]@row = 1, "Blue", IF([% Complete]@row = 0, "Black", IF([Due Date]@row > TODAY(10), "Green", IF([Due Date]@tow >= TODAY(), "Yellow", "Red"))))

I do not believe, however, there is a symbol option for Green/Yellow/Red/Blue/Black. Your options are Green/Yellow/Red/Blue or Green/Yellow/Red/Gray.

• ✭✭✭✭✭✭

I agree with @Carson Penticuff but with one question...

What if the % complete is more than 0% but less than 100%, and the Due Date is 7 days away? You specified more than 10 days away would be green and less than 5 days away would be yellow (until in the past when it turns red), but you didn't specify anything for 6, 7, 8, 9, or 10 days away.

• Overachievers

Hi @Malinda

Try this, where [Complete] is your % Complete column and [Due Date] is your date column.

=IF(Complete@row = 100, "Blue", IF(AND([Due Date]@row > TODAY() + 10, Complete@row < 100, Complete@row > 0), "Green", IF(AND([Due Date]@row < TODAY() + 5, [Due Date]@row > TODAY(), Complete@row < 100, Complete@row > 0), "Yellow", IF(AND([Due Date]@row < TODAY(), Complete@row < 100, Complete@row > 0), "Red", "Not started"))))

I've left your requirement for Black to read Not Started as I believe there are only 4 coloured symbols, so the ) complete will always be whatever text you add here.

Hope that helps

• ✭✭✭✭✭✭

I hope you're well and safe!

That won't work because,

Smartsheet looks at the numbers in a column formatted for percentages as values between 0 and 1. You'd need to use decimal values instead for it to work

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1

Depending on your country/region, you must exchange the comma for a period.

It would have to be updated with 1 instead of 100, see below.

=IF(Complete@row = 1, "Blue", IF(AND([Due Date]@row > TODAY() + 10, Complete@row < 1, Complete@row > 0), "Green", IF(AND([Due Date]@row < TODAY() + 5, [Due Date]@row > TODAY(), Complete@row < 1, Complete@row > 0), "Yellow", IF(AND([Due Date]@row < TODAY(), Complete@row < 1, Complete@row > 0), "Red", "Not started"))))

I hope that helps!

Be safe, and have a fantastic weekend!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• Thank you for all your help. The IF(AND statements worked perfectly.

Heads up to all if you want to add different colour symbols you can use UNICHAR(9899) is black. Each colour has a different UNICHAR number.

• Overachievers

You make a good point, my sheet didn't have the [Complete] column formatted as percentage (I never use percentage formatting out of habit from other programs). But yes, would need to use 1 and 0 as you've stated.

Kind Regards

• ✭✭✭✭✭✭
edited 04/05/24

Excellent!

Happy to help!

Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭✭✭

Thanks!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• Overachievers

@Malinda Thanks for the info, that's what I love about these forums, constantly learning new things! 😀

• ✭✭✭✭✭✭

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!