How do I add months to a date without there being a month duration option?

I'll try to keep from getting too convoluted. On my main sheet I have a "Start Date" that pulls a date from another sheet. I have built a long checklist of items that are due based on this "Start Date". Each item has a different length of duration. There are a couple items at the end of the list that I was looking to use Months as duration.

I have had some luck creating a formula in Excel and then, with some tweaks, bringing it into Smartsheet, however I am stuck with this one. Here is my Excel formula where I base my duration on an alpha number character: 5d, 5w, 5m. And it works.

=IF(RIGHT(E4)="m",DATE(YEAR(E3),MONTH(E3)+LEFT(E4),DAY(E3)),(WORKDAY(E3,(IF(RIGHT(E4)="w",(LEFT(E4))*5,(LEFT(E4))*1)))))

And here is my formula within Smartsheet that works for days/weeks:

=IFERROR(WORKDAY([Start Date]@row, (IF(RIGHT(Duration@row) = "w", VALUE(LEFT(Duration@row)) * 5, VALUE(LEFT(Duration@row)) * 1))), "")

Here is the formula I tried for Months duration: (I know the names are different, just because I was trying it on a practice sheet before I make a mess of my main sheet.

=IFERROR(IF(RIGHT(Duration@row = "m", DATE(YEAR(Start@row), MONTH(Start@row) + VALUE(LEFT(Duration@row)), DAY(Start@row)), (WORKDAY(Start@row, (IF(RIGHT(Duration@row) = "w", (LEFT(Duration@row)) * 5, (LEFT(Duration@row) * 1))))), "")))

Edit: I am getting an error #UNPARSABLE, and I think it may be because of the way I am trying to put a value to the "Month" function.

Sorry if it's a mess. Definitely still getting used to Smartsheet from Excel.

Find more posts tagged with

Formulas

Accepted answers

All comments

dojones

Extracting duration period using Right isn't working how you think. Try this to see what I mean. Place a column next to your duration that is =Duration@row. 5d=5, 2w =10, 1m =0.00208 (converts to 1 sec per hour). When using right, you are actually extracting from the number not the "d" or "w".

In the end, stick to days for the duration.

Nathan Fraser

Oh, I forgot that m is minutes in duration, but I don't think it applies (I can change the letter to any other letter) as I am using it in a formula, and don't even have the dependencies active.

Using only days doesn't really work for what I'm looking for as the project I am trying to use this for extends out to 9 months with period audits at 2 months and 7 months. I am using this workspace as a template (Save as New), so the start date will always be different when a new workspace is created.

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.