Creating Multiple If Statements

Malinda · 04/05/24

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.

Gillian C · 04/05/24

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

Carson Penticuff · 04/05/24

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.

Paul Newcome · 04/05/24

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.

Gillian C · 04/05/24

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

Andrée Starå · 04/05/24

Hi @Gillian C

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!

Malinda · 04/05/24

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.

Gillian C · 04/05/24

Hi @Andrée Starå

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

Andrée Starå · 04/05/24

@Malinda

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!