Date Calculation: Subtract Days from Original Date

Kelly Moody

So, I'm a beginner in Smartsheets. I'm trying to build a sheet that has one "parent" date that the rest of the dates are "born" off of (e.g. T-27 days, T+14 days, etc.).

I'd like to avoid using Duration because the start date isn't firm (but the end date is).

I've been all over the community help section, but no matter what I do, I can't get the solution to work. I tried watching 2 videos and 4 different methods. I know I'm missing something, but I can't even begin... again, super newbie.

Paul Newcome

It looks like you have dependencies turned on. If this is the case, you will not be able to use formulas in any of the columns referenced by the dependencies. You will either need to play with the durations, or turn off the dependency settings and use formulas. If you decide to work with formulas, I'd be happy to help (attempt to) work out a solution.

Paul Newcome

Correct. I would just make one slight adjustment. Instead of referencing an actual row number, reference the Parent row.

=[Start Date]2 + 21

changes to

=PARENT([Start Date]@row) + 21

This way you don't have to worry about updating any row references if you should happen to add any rows in or anything.

Paul Newcome

It looks like you have dependencies turned on. If this is the case, you will not be able to use formulas in any of the columns referenced by the dependencies. You will either need to play with the durations, or turn off the dependency settings and use formulas. If you decide to work with formulas, I'd be happy to help (attempt to) work out a solution.

Kelly Moody

Thank you! That makes complete sense, and I can confirm that was the issue.

So, in theory, if I used the above formula for every field (besides the "parent" field) all I would need to do (when I templatize this) is to change the parent field, correct?

Paul Newcome

Correct. I would just make one slight adjustment. Instead of referencing an actual row number, reference the Parent row.

=[Start Date]2 + 21

changes to

=PARENT([Start Date]@row) + 21

This way you don't have to worry about updating any row references if you should happen to add any rows in or anything.

Kelly Moody

One last question: If I did want to keep dependencies and predecessors, is it possible to do something similar where I would only have to update one parent number?

Update: Now that I think about it, using predecessors and dependencies would achieve the same result... Keep linking all the rows to predecessors then ensuring the duration was corrected to ensure the end date would be the same and just move the start date. Cool. I guess I answered my own question.

Let me know if there's a better way of doing this!

Paul Newcome

Glad you figured it out. Haha.

To answer your question exactly though, I would have to say "No". The parent rows are a summary of the child rows.