Update a Set of Dates when a Specific Date Changes

ZachB

Hello Smartsheet Community,

I currently have a sheet that keeps track of our project dates and tasks, similar to how Microsoft Project works. We have one column with a date value labeled "Field Date" and another Column labeled "Work Date" with a date value as well. The idea is that when we update the Field Date value to some date in the future, it updates the Work Dates accordingly.

For example lets say I update the Field Date to 08/27/22, in turn, the task below it should update to that specified date plus 2 days, so 08/27/22. This goes for the rest of the date values in the "Work Date" column all with various number of days to be completed after Field Date is set. Below is a snippet of what the sheet looks like.

I have tried Workflows, different formulas from IFERRORs, to VLOOKUPS, and INDEX but have had no luck with any of them. I've been able to get this working in Excel with the formula below but can't transcribe it into Smartsheet. Hoping someone can point me in the right direction. Thanks so much!

=IFERROR(LOOKUP(2,1/(M3:M11<>""),M3:M11)+2,TODAY()+10)

Paul Newcome

Try enabling dependencies. This will allow you to set duration and predecessors.

Paul Newcome

Try enabling dependencies. This will allow you to set duration and predecessors.

ZachB

https://community.smartsheet.com/discussion/comment/339458#Comment_339458

Hey Paul,

Thanks so much for your response, I had no idea Smartsheet had that functionality. I was able to get my sheet working as intended after some further research on Smartsheet's Dependencies. Thanks again!

Paul Newcome

Happy to help. 👍️