Conditional Formatting to change status?

scott williams
edited 12/09/19 in Smartsheet Basics

Hi,



I'd like to be able to change the row status (planning/in progress/complete/overdue) depending on a required date in another column in that same row.



I have a report set up to report all overdue tasks and I'ld like to automate it more to change the status of all overdue tasks without any input from me.



Is this possible?  I've explored the conditional formatting options but nothing seems to apply.



Thanks.

Scott.

 

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/18/19

    Hi Scott,

    It's possible to set up with an IF formula or multiple IF formulas depending on your need and sheet structure.

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Hi Andree,



    Thanks for the quick reply, I always forget about the ability to program cells.



    Here's a screen shot of the sheet I'm working on.  I'd like to be able to change the status of a row to overdue if either the required date or delivery date is past due.

    Here's a screenshot, sorry not sure how to share the sheet?



     

    Screen Shot 2019-03-18 at 12.19.56 PM.png

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/18/19

    Happy to help!

    Try this.

    This will show Overdue when true or nothing if it's not true. If you change the Status manually it will delete the formula in that cell. 

    Maybe you'd want to build the formula to change the all the status types automatically?

    =IF(OR([REQUIRED DATE]@row <= TODAY(); [DELIVERY DATE]@row <= TODAY()); "Overdue"; "")    

    The same version but with the below changes for your and others convenience.    

    =IF(OR([REQUIRED DATE]@row <= TODAY(), [DELIVERY DATE]@row <= TODAY()), "Overdue", "")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Best,

    Andrée

    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.