Lead Time (Months) driven dates

JWood
JWood ✭✭
edited 08/30/23 in Formulas and Functions

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!