Hello, Novice needing formula help.

Options

I have searched help topics but still find myself challenged. I would like the Date due cell to turn red if the Date Due has passed. Also, would like the Date Due cell to turn green if the Check In Date was before the Date Due. I basically need a visual reminder if the Date Due was missed and needs attention, but do not want it to remain red if there is a date entered into the Check In Date cell. Please excuse my ignorance with this.

Thank you kindly in advance for any help you are willing to provide.

Best Answer

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

    Hi @C. Phillips

    I hope you're well and safe!

    To add to Tariq's excellent advice/answer.

    For the second rule, you must add a so-called helper column with a formula that checks if the check-in date was before the date due.

    Try something like this. (in a checkbox column that you reference in the rule, and you can convert it to a column formula)

    =IF([Check In Date]@row <> "", IF([Check In Date]@row < [Date Due]@row, 1))
    

    Did that work/help?

    I hope that helps!

    Have a fantastic weekend & Happy Holidays!

    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.

Answers

  • Hi C!

    You may like to try creating 2 conditional formatting rules for Due Date column:

    If Due Date is in the past and Check In Date is blank then apply this format to the Due Date Column

    If Check in date is a date then apply this format to the Due Date Column

    I have attached a screen shot and hope that helps.

    You can add the "And" condition by clicking on the dropdown symbol on the rule and play with the conditions for fine tuning or adding more logic.

    Best of luck!

    Tariq Shaikh

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

    Hi @C. Phillips

    I hope you're well and safe!

    To add to Tariq's excellent advice/answer.

    For the second rule, you must add a so-called helper column with a formula that checks if the check-in date was before the date due.

    Try something like this. (in a checkbox column that you reference in the rule, and you can convert it to a column formula)

    =IF([Check In Date]@row <> "", IF([Check In Date]@row < [Date Due]@row, 1))
    

    Did that work/help?

    I hope that helps!

    Have a fantastic weekend & Happy Holidays!

    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.

  • C. Phillips
    Options

    Tariq & Andrée,

    Thank you both very kindly for taking the time to help with my issue. Everything worked perfectly!

    Thanks again and have a great day!


    C. Phillips

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @C. Phillips

    Excellent!

    You're more than welcome!

    Remember! 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!