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!

Tags:

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:


    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"

  • 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:


    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"

  • 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"

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:


    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"

  • 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.

  • 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.


    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"

  • 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:




    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.

  • 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:


    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"

  • @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.

    Thanks in advance!

  • 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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!