Lead Time (Months) driven dates

JWood

Greetings,

I'm working on a project plan template that uses lead-time (in months) for each task that leads to a recurring annual event.

I'd like to have the actual start dates for each task driven by the actual date of the event when it's entered into a field.

So for instance, if the event is scheduled a year from now on 8/30/24, I'd like for the start date of 8/30/23 to be automatically populated in the start field for a task with a -12 month-lead time.

Or 10/30/23 for a task that has a -10 month lead-time.

I can't seem to get SmartSheet to recognize the syntax I'm entering in the "Start" field for each task....which I'm sure is wrong.

Paul Newcome

Give this a try:

=IFERROR(DATE(YEAR(Start1) + ROUNDDOWN((MONTH(Start1) + LTM@row) / 12, 0) + IF(IF(MOD(MONTH(Start1) + LTM@row, 12) = 0, 12, MOD(MONTH(Start1) + LTM@row, 12)) = 12, -1) - IF(AND(ABS(LTM@row) - MONTH(Start1) <> 12, LTM@row < 0, ABS(LTM@row) > MONTH(Start1)), 1, 0), IF(MOD(MONTH(Start1) + LTM@row, 12) = 0, 12, MOD(MONTH(Start1) + LTM@row, 12)), 1), DATE(IF(MONTH(Start1) - ABS(LTM@row) < 1, YEAR(Start1) - 1, YEAR(Start1)), IF(MONTH(Start1) - ABS(LTM@row) < 1, MONTH(Start1) + (12 - ABS(LTM@row)), MONTH(Start1) - ABS(LTM@row)), DAY(Start1)))

Paul Newcome

Are you using dependencies or formulas?

JWood

https://community.smartsheet.com/discussion/comment/393304#Comment_393304

I have the following columns: Task; Lead Time; Start Date; and a single stand alone field "Event Date"

Task and Lead Time are static since this is a template.

So for row 2

In the "Start Date2" field I put = Event Date - (Lead Time2 *30).

So if the event date entered was 10/1/24, I was hoping to return a -12 lead time start date of 10/1/23.

...no joy

Paul Newcome

Are you able to provide screenshots for context?

JWood

Sure. Stand by

JWood

I have this formula in the "Start4" field. I need it to refer to the Retreat date entered above in "Start1" and subtract the -12 in the LTM4 (Lead-Time Months) field to render a start date of 8/31/23.

Paul Newcome

Give this a try:

=IFERROR(DATE(YEAR(Start1) + ROUNDDOWN((MONTH(Start1) + LTM@row) / 12, 0) + IF(IF(MOD(MONTH(Start1) + LTM@row, 12) = 0, 12, MOD(MONTH(Start1) + LTM@row, 12)) = 12, -1) - IF(AND(ABS(LTM@row) - MONTH(Start1) <> 12, LTM@row < 0, ABS(LTM@row) > MONTH(Start1)), 1, 0), IF(MOD(MONTH(Start1) + LTM@row, 12) = 0, 12, MOD(MONTH(Start1) + LTM@row, 12)), 1), DATE(IF(MONTH(Start1) - ABS(LTM@row) < 1, YEAR(Start1) - 1, YEAR(Start1)), IF(MONTH(Start1) - ABS(LTM@row) < 1, MONTH(Start1) + (12 - ABS(LTM@row)), MONTH(Start1) - ABS(LTM@row)), DAY(Start1)))

JWood

Wow! I'll give it a whirl! Thank you!

JWood

@Paul Newcome thanks for the help! I got it to work!

Paul Newcome

Happy to help. 👍️