Dynamic Dates

Hello,


I'm working on a workflow where the time to complete a task within a project is static but the dates we start the project are dynamic. Is there an easy way to program that? Example below...


*Project A starts on 1/1/2022*

Task1 = 3 months to complete so the due date is 4/1/2022. Range = 1/1/2022 - 4/1/2022

Task2 = 3 months to complete with a 1 month lag so the due date is 5/1/2022. Range = 2/1/2022 - 5/1/2022

Task 3 only takes one month but can start until Task 2 is done so the due date becomes 6/1/2022. Range = 5/1/2022 - 6/1/2022


*Project B is exact same but starts on 6/1/2022*

Task1 = months to complete so the due date would be 9/1/2022. Range = 6/1/2022 - 9/1/2022

Task2= Due date is 10/1/2022. Range = 7/1/2022 to 10/1/2022

Task3= 10/1/2022 - 11/1/2022


My goal if possible is to have a reference cell at the top of the page with a "start date" and then all subsequent tasks would have the calendar range update accordingly. I'm unsure if something like this is possible is SmartSheet.


Thanks,

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Yeah, I think this is easy to do...


    Set up your sheet in gantt form. Then add predecessors as per what you have stated. Should look like the below if I'm understanding you correctly.

    Syntax for predecessors is: row reference - relationship - lead/lag

    Where relationships are:

    FS = Finish-Start - your task must finish before the next task starts

    FF - Finish-finish - so both tasks finish at the same time.

    SS - Start-start - both tasks start at the same time.

    SF - Start finish - your task starts once the other one finishes (you dont have to write this one in as its the default.


    So for project one task two the pred = 1FS+30 as there is a 30 day lag.




  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @SjopsonTMO,

    You can use formulas in the Start Date and End Date cells.

    Task 1

    =[Start Date]1

    =DATE(YEAR([Start Date]1), INT(MONTH([Start Date]1) + 3), 1)

    Task 2

    =DATE(YEAR([Start Date]1), INT(MONTH([Start Date]1) + 1), 1)

    =DATE(YEAR([Start Date]1), INT(MONTH([Start Date]1) + 4), 1)


  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    @Devin Lee only issue with this solution is that it only works if your project doesn't slip into the next year. Once is does you end up having to modify a relatively simple formula to a lengthy nestled if statement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!