formula that when completed next start date is +330 days

I am trying to figure a formula that when I actually complete something, that the start date row will trigger the next time it needs to be done.

Example:

I have start date, due date, and actual complete date.

Start date would be 2/1/21, with a due date of 2/15/21.

I'm then filling in when the task is actually completed, which hopefully will be close to due date. Say it was 2/16/21. I would like the next start date to reflect a new date to start of +330 days.

Plan is to use this for annual program review. Set reminder to review it, time to get done, when it actually gets done then auto generate new start date for next year to do all over again.

Unless someone has a better way to structure this, I am open to suggestions.

I have 15+ programs to monitor and ensure they get reviewed annually (along with some other tasks that repeat as well).


Appreciate any feedback. THANK YOU !

Answers

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

    Hi @Tammy Luther

    You'll need to add a so-called helper column for the Start Date because you can't use a formula and manually change the data.

    Try something like this.

    Add a helper column, Start Date (Helper)

    And add this formula to the Start Date column.

    =IF([Complete Date]@row <> "", [Complete Date]@row + 330, [Start Date (Helper)]@row)
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) 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.

  • I am trying to figure out a formula that when I actually complete something, it will trigger the next start date task.

    Example:

    I have start date, due date and complete date.

    Start date would be 2/1/21, with a due date of 2/10/21.

    I'm then filling in when the task is actually completed, 2/15/21, I would like the next start date to reflect a new date to start of 2/16/21. (instead of 2/11/21).

    The above formula did not help me as I don't want to add days.

    Thank you this would help a lot if there is a simple formula!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!