Lead Time (Months) driven dates

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.

Find more posts tagged with

Accepted answers

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

All comments

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:

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.