Formula for lights
Hi all,
I have a sheet that contains fields for "Preparer Due Date" and "Preparer Status" and am trying to add in the following logic to a formula:
Blue = completed
Green = task not started or in process that are due more than 3 days away
Yellow = task not started or in process that are due within the next 3 days
Gray = task Not Started and not due within the next 3 days
Red = task not started or in process that are past due
Thanks in advance!
Best Answers
-
Hope you are fine, please try the following formula and convert it to column format formula:
=IF([Preparer Status]@row = "completed", "Blue", IF(AND(OR([Preparer Status]@row = "not started" , [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY()), "Red", IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY(-3)), "Green", IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row >= TODAY(-3), [Preparer Due Date]@row <= TODAY()), "Yellow"))))
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Please try the following formula:
=IF([Preparer Status]@row = "completed", UNICHAR(128309), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started",[Preparer Status]@row ="in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994))))))
The following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Please try the following formula to populate the yellow :
=IFERROR(IF([Preparer Status]@row = "completed", UNICHAR(128309), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994)))))), "")
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hope you are fine, please try the following formula and convert it to column format formula:
=IF([Preparer Status]@row = "completed", "Blue", IF(AND(OR([Preparer Status]@row = "not started" , [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY()), "Red", IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY(-3)), "Green", IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row >= TODAY(-3), [Preparer Due Date]@row <= TODAY()), "Yellow"))))
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you Bassam. This is great but I need the circle to be gray if the task is not started and not due within the next 3 days. If not started and due in the next 3 days, it should be yellow.
-
Unfortunately in smartsheet symbol column you don't have this option to have 5 colored balls. maybe if you select different symbol shape you can have more options.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I hope you're well and safe!
To add to Bassam's excellent advice/answer.
Here's a possible workaround or workarounds
You can use other symbols by using UNICHAR, CHAR, or Emojis.
More info:
A great resource that Paul Newcome put together
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
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: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.
-
Please try the following formula:
=IF([Preparer Status]@row = "completed", UNICHAR(128309), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started",[Preparer Status]@row ="in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994))))))
The following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hope all is well!
For some reason it is not populating a circle for this task which is in process and due in 3 days. I'd like to see this appear yellow if possible.
Thanks in advance!
-
Please try the following formula to populate the yellow :
=IFERROR(IF([Preparer Status]@row = "completed", UNICHAR(128309), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994)))))), "")
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!