Insert a date six weeks before key date

andrew.taylor37161 ✭✭✭
edited 12/09/19 in Smartsheet Basics

Hi All

I am trying to create a formula to insert a date six weeks before a key date. This earlier date will be the commencement date of the tasks.

Example: We have a building that has an end of financial year on 30 July 2018. I need to start the first task mid June and also run this start date as the basis for the predecessors.



  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Andrew,

    You'll need to change the column names to suit, but this should give you what you need:

    =WORKDAY([Start Date]1, -30)

    this works because there are 5 working days in a week and therefore 30 working days in 6 weeks.

    There is one big caveat here though. Based on your original post, it looks like you may want to use these columns and the above formula to drive predecessor dates and the overall project Start Date. If you configure a date column (e.g. Start Date) in the Project Settings, this is then used by Smartsheet to calculate project dates. As such, Smartsheet removes the ability to use formulas in these columns as it inserts super secret hidden system formulas (so things like Duration and Finish Date work as expected). You cannot change this behaviour if these columns are selected in the Project Settings.

    Kind regards,

    Chris McKay