Update a Set of Dates when a Specific Date Changes

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!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!