Can you auto populate task dates based on the entire project end date?


I have different marketing tasks where their completion dates revolve around an entire projects completion date. For example, there are tasks that need to be completed 10 days before project completion date, 20 days prior, 30 days prior, etc.

Is there a formula I can use that auto fills task due dates based on the project completion date using "days prior".


  • Mark Kapsa
    Mark Kapsa ✭✭✭


    You can use the Predecessors column to create links between the different tasks, this will allow tasks to move in relation to each other. The sheet would need to be set up as a schedule and the Predecessors column is a system column for that type of sheet. There are several ways to connect the tasks as listed below.

    Finish to Start (FS) - In a Finish to Start relationship, the second task can only start once the first task is completed.

    Start to Start (SS) - In a Start to Start relationship, the second task starts at the same time as the first task.

    Finish to Finish (FF) - In a Finish to Finish relationship, the second task completes at the same time as the first task.

    Start to Finish (SF) - The Start to Finish is the least used of the predecessors, when using this link the start of the first task controls the finish of the second task.

    In addition to the those connections you can add offset durations to move the tasks relative to each other. For example to start a task 10 days after the finish of the first task, this would be 1FS+10 and that would be placed in the 2nd task predecessor cell.

    For your case of working back from a completion date you would probably use the SF link connection with a negative offset, so for task 1 it would be 2SF-10 to have task 1 finish 10 days from the start of task 2, moving it back in time.

    Hope this helps.

    Mark Kapsa

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

    Hi @Alexa Upper,

    To add to Marks excellent answer.

    Here’s some excellent resources.

    This might help as well.

    I hope that helps!

    Be safe and have a fantastic weekend!


    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!


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

    W: | | 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!