How to change status based on start/end dates/if completed was checked or not.

Hi there,

I've been reviewing past topics but haven't been able to get those formulas to work. I have a sheet where there are start/end dates, and a completed check box column. I'm having trouble figuring out how to create a formula where if start date is in the past and end date is in the future, having it automatically change the status to in progress, same for if the end date is past and the check box is not checked, having it automatically changing the status to delayed, etc. Can you please help me with this?


Thanks in advance!

Caitlin

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are able to provide a detailed list of the different criteria for each status, we may be able to figure something out.


    I assume you want

    "Complete": Checkbox is checked

    "In Progress": Today is between the Start and Finish Date

    "Delayed": Today is past the Finish Date


    What are the other statuses and their criteria such as what if the start date is in the future?


    What are your three column names as they are in the sheet?

  • Hi Paul,

    Thanks for your quick response! That is correct. Our other status columns include "Not Started" (if status is in the future). Also, "Canceled" "Awaiting feedback" "On hold" but I'm wondering if those will have to be manual processes since they aren't necessarily date-related. The three column names are "Complete" (with the checkbox), "Start Date", "End Date".

    Thanks!

    Caitlin

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here's the catch... You can only have a formula OR manual entry. Once you manually enter data into a cell to include selecting from a dropdown, it will overwrite/delete the formula.


    To automate the Status, you will need to come up with some other option for those 3 choices of "Cancelled", "Awaiting Feedback", or "On Hold" (maybe a separate dropdown or checkboxes). Once you figure out how you want to handle that part, we can work that criteria in to the overall formula that would automate all statuses.

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

    @Caitlin Bowman

    Hi Caitlin,

    The third-party service, Zapier, could be an excellent option for this scenario. Is that an option for you?

    Would that work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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!