Formula for lights

Brennan Montoni
Brennan Montoni ✭✭
in Formulas and Functions

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


image.png


Thanks in advance!

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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:

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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:

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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)))))), "")

    PMP Certified

    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"

    · Share on FacebookShare on Twitter

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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:

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Brennan Montoni
    Brennan Montoni ✭✭

    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.

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Brennan Montoni 

    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.

    1.jpg


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Brennan Montoni

    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:

    https://help.smartsheet.com/function/unichar


    https://help.smartsheet.com/function/char


    https://getemoji.com/


    A great resource that Paul Newcome put together

    https://app.smartsheet.com/b/publish?EQBCT=82f8fe0936a54758a3afa4b584acde07


    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.

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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:

    image.png


    PMP Certified

    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"

    · Share on FacebookShare on Twitter
  • Brennan Montoni
    Brennan Montoni ✭✭

    @Bassam Khalil


    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.

    image.png

    Thanks in advance!

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Brennan Montoni 

    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)))))), "")

    PMP Certified

    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"

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!