Changing a start date, but keeping subsequent dependent dates within "months"

I work in sales for a construction company. Our forecasted timelines for projects are often pushed, and that affects my monthly revenue projections. I've broken down our forecasted project timelines by month per row, but if the start date gets pushed, I'd like for the dates of the subsequent rows/months to STAY WITHIN those months. Can anyone advise a formula that makes this adjustment possible, based on a set number of days for the project? The attached screenshots show the current timeline (April 1 start date, 105 days), the problem when I adjust the start date of Month 1 (April 12), and the goal showing an adjusted start date (April 12) but maintaining the dates as per the subsequent months based on an overall timeline of 105 days.

(I do see a potential issue: if the start date gets pushed to a point where we'd push the entire timeline to a new month that is not accounted for in subsequent rows.)


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/11/21

    Hi @Sandy Glassberg

    There currently isn't a way to set up this behaviour in your Project Settings when you have dependencies enabled; as you've found, adjusting the start date for a task will then auto-adjust the end date based on the Duration of that task. See this Help Article for more information: Enable Dependencies and Use Predecessors.

    You could potentially disable dependencies in the sheet and then create your own formula to calculate the Duration, such as: =NETWORKDAYS(Start@row, Finish@row)

    In this instance, you wouldn't have any predecessors and the start and end dates for each row would be independent. This means that if you change the Start date it wouldn't affect the end date at all, but it would update any formulas looking at those two dates. Does that make sense, and would that work for you?

    If you decide to go the route of disabling dependencies and find that you need help with any of the formulas please let me know!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!