How can I autopopulate end date with months duration without using dependencies?

Paul Newcome

@Debbie Sawyer I plugged yours in so I could compare side by side just to see exactly how yours was working, and I ended up getting an #INVALID VALUE error at 23 and 35 months.

My formula is in the [New Date] column, and I threw yours into the [Other New Date] column (copy/paste with the same column names). Did you want to take a look and see if we could narrow down (an solve) why?

NOTE: When I use a date in December the error pops up on 23 and 36 months.

Debbie Sawyer

@Paul Newcome

Doh!

It's down to when the number of months are exactly a year it returns a MONTH 0 in the result.

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration (Months)]@row, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + [Duration (Months)]@row) / 12), ((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)), DAY([Start Date]@row)))

If I break this down from your example it is:

((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12))

((1+23) - (INT((1+23)/12)*12)

(24-(2*12)

(24-24) = 0

So the month = 0 which is invalid... :(

Can we nest add another IFERROR for when this happens? My brain hurts..!

Paul Newcome

Ok. So the "Why" makes perfect sense. Is there an EFFICIENT way we can account for that? Maybe an IFERROR statement?

Paul Newcome

What about...

IFERROR(original string, 12)

??

I haven't plugged it in yet to see, but shouldn't that work?

Debbie Sawyer

@Paul Newcome

Fixed it

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration (Months)]@row, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + [Duration (Months)]@row) / 12), IF(((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)) > 0, ((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)), 12), DAY([Start Date]@row)))

Paul Newcome

Looks like you used

IF(original string > 0, original string, 12)

Am I reading that right? I thought about going the route of an IF too, but I haven't quite picked apart exactly how your formula is meshing together yet. Haha.

Debbie Sawyer

Yes - that is what I did - but I've now figured out it is still broken :(

When the End Date month hits 12 it increments the year a month too early! :(

Paul Newcome

Hmm... That actually ended up being one of the bigger roadblocks for me as well when I was building mine out. I feel like this is where I first started experimenting with the MOD function for mine.

You have the YEAR(............) portion. You could drop that into an IF like so

IF(MOD([Duration (Months)]@row, 12) = 0, YEAR(...........) - 1, YEAR(..............))

Use the MOD to quickly identify those that are at the 12 month increment. If it is a 12 month increment, subtract 1 from the year, otherwise output the original year string result.

Debbie Sawyer

Finally! ha ha

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration (Months)]@row, DAY([Start Date]@row)), DATE(IF(MOD(MONTH([Start Date]@row) + [Duration (Months)]@row, 12) = 0, YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + [Duration (Months)]@row) / 12) - 1, YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + [Duration (Months)]@row) / 12)), IF(((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)) > 0, ((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)), 12), DAY([Start Date]@row)))

Now I know the MOD() function better, I'll go back and look at yours again! ha ha

Thanks @Paul Newcome

@Michaela Kamenska - Thank you for letting me and Paul use your thread to help me get this formula working (for you!) ha ha

Paul Newcome

@Debbie Sawyer Haha. It took me a little while to get used to the MOD function, but once you get a feel for how it really works, it can have a lot of different uses.

Basically it generates a number that is the leftover when dividing one number by the other.

=MOD(n, 12)

Will take n and divide it by 12 (or multiple of 12 depending on n) and output what is leftover.

Since you are already working in months, go ahead and use that. Reference the [Duration (Months)] column for n, dragfill it down all the way through 36+ months, and I think you will see what I am talking about. It really is a very handy little function.

I'm still going to try to understand your version a little better though. Haha

@Michaela Kamenska Yes. Thank you for letting us geek out for a little bit. Hahaha

Debbie Sawyer

@Paul Newcome - in a past role I used to be the Crystal Reports analyst for a 24x7 service desk function. I had to come up with many formulas, but one in particular was to take an SLA and compare it to action durations and work out manhours used and manhours remaining per incident etc. (When I worked out that particular function I ran around the office cheering (got a lot of strange looks from everyone who really didn't appreciate the satisfaction of getting the right results)! ha ha it was so complicated, when printed it almost took up a whole A4 sheet!!) I used the Truncate and Remainder functions alot in that! Been looking for a remainder function here but didn't realise it was called MOD!

Great :)

Paul Newcome

Hahaha. Yeah. MOD is relatively new to Smartsheet. I can't remember when exactly, but it just got rolled out some time last year. You should have seen my workaround for it until then.