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

Nathan Fraser

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.

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.