Creating Color Coded Column

Help me out here guys... I am trying to create a color coded formula that would produce the following results: If date file received is not blank – populate green.

If date file received column is blank AND…..

If today’s date is 1 day older than expected date column – populate yellow

If today’s date is 2+ days older than expected date column – populate red.

I took a stab at a formula and received an error message:

=IF([Date File Recd]@row, "Green", IF(AND([Date File Recd]@row = "", [Date File Expected]@row > TODAY(), "Yellow", IF(AND([Date File Recd]@row = "", [Date File Expected]@row > (2), "Red"))

Best Answers

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    edited 04/20/21 Answer ✓

    Without knowing the details, your formula should look something like this:


    =IF(NOT(ISBLANK([Date Received]@row)), "Green", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 2), "Red", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 1), "Yellow")))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Beronica Muller

    I hope you're well and safe!

    Try something like this.

    =IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF([File Canceled]@row = 1, "Purple"))))

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

Answers

  • Cody Holmes
    Cody Holmes ✭✭✭✭

    Are you trying to do conditional formatting on a column, or are you trying to make the formulate generate the red, yellow, green symbols in a column? And what is the column name that you are trying to perform this on?

  • Cody Holmes
    Cody Holmes ✭✭✭✭
    edited 04/20/21 Answer ✓

    Without knowing the details, your formula should look something like this:


    =IF(NOT(ISBLANK([Date Received]@row)), "Green", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 2), "Red", IF(AND(ISBLANK([Date Received]@row), TODAY() - [Expected Date]@row >= 1), "Yellow")))

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    @Cody Holmes I was trying to formulate generate the red, yellow and green symbols in a column. This formula is exactly what I needed. Thank You!!

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    @Cody Holmes I now need to add another "IF" to the formula. I need to add the color purple for items that have a check box in the File Canceled column. I used this formula #UNPARSEABLE error. Could you take a look at it and tell me what did I do wrong?

    =IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF(AND([File Canceled]@row = 1), “Purple”)))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Beronica Muller

    I hope you're well and safe!

    Try something like this.

    =IF(NOT(ISBLANK([Date File Recd]@row)), "Green", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 2), "Red", IF(AND(ISBLANK([Date File Recd]@row), TODAY() - [Date File Expected]@row >= 1), "Yellow", IF([File Canceled]@row = 1, "Purple"))))

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

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    @Andrée Starå Would I need to begin the formula with the last IF statement first? My reasoning for asking is because if the File Canceled column is checked, then the row would need to be purple. I tested this and it does not seem to work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!