Less than 30 days forrmula


Hi there,

Have a question. I am wondering the best way to approach this. Use case = If "Expires' column date is less than 60 days away - change the status value in the dropdown to 'caution'


Can anyone assist with this??

Would be greatly appreciated



Answers

  • Sing C
    Sing C ✭✭✭✭✭✭

    Hi @Trav25, there are a couple options I can suggest, depending on your circumstances:

    1. Create an automation to update the status column to your desired value based on a set of conditions being met, and you could trigger this to run daily. This article (with video) gives you more information about creating automations.
    2. Create a column formula in the status column to set the value based on an IF statement.

    As an example, you could use this formula:

    =IF(NOT(ISDATE(Expires@row)), "Required", IF(Expires@row < TODAY(), "Expired", IF(Expires@row < TODAY(60), "Caution", "Up-to-Date")))
    

    An explanation:

    • The NOT(ISDATE) portion will set status to "Required" if Expires is not a date
    • If Expires is in the past then status = Expired
    • If Expires is in the next 60 days then status = Caution
    • If Expires is anything else, then status = Up-to-Date

    The thing to note with using a column formula is if you rely on manually setting the status in some circumstances a column formula won't work for you because you can't overwrite one row of a column formula with a manual value. The example above should cover all possible values of 'Expires' (no date, in the past, in the next 60 days, all other dates) and should therefore not require any manual entry. Obviously, adjust the formula as needed to fit your specific business rules.

    I hope that helps. Let me know how it goes!

    If my comment addresses your query, please support the Community by marking it as an Accepted Answer. This helps others find the solutions like yours more quickly. I would also be grateful for your "Insightful" or "Vote Up".

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

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

    Hi @Trav25

    I hope you're well and safe!

    Try something like this.

    Add a Workflow for when a date is reached.

    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, 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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Trav25
    Trav25 ✭✭✭✭


    Hi @Andrée Starå, @Sing C ,

    Thank you to you both for getting back to me - much appreciated!

    Was playing around with the automation yesterday and came up with this (below) - seems to function as expected.

    Will try both you methods also.

    Trav



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

    @Trav25

    Excellent!

    You're more than welcome!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Sing C
    Sing C ✭✭✭✭✭✭

    You're welcome @Trav25!

    Let us know how you get on.

    Have a wonderful day!

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!