Lead Time (Months) driven dates
Greetings,
I'm working on a project plan template that uses leadtime (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 monthlead time.
Or 10/30/23 for a task that has a 10 month leadtime.
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.
Best Answer

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)))
Answers

Are you using dependencies or formulas?

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

Are you able to provide screenshots for context?

Sure. Stand by

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 (LeadTime Months) field to render a start date of 8/31/23.

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)))

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

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!