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.
Best Answer
-
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
Answers
-
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.
-
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
-
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!
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.
-
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
-
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.
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!