At months on to a date

Cathy Fraser

I am looking to add months onto a date based on what is shown in the column highlighted in the screen shot. It is simple in excel using "Edate" but I can not seem to get it working in Smartsheet

Thanks in advance

MCorbin

I'm making the assumption you'd want to come up with the same date, but 12 months later, right? (or whatever # of months is in the 2nd column?)

In the example above - you'd want to see 27/04/21 ?

Because months can have varying #s of days, it can be tricky, but here's what I've done that works for me....

Create a helper column that gives you a date in the middle of the month (same month/year as your original date) - I usually use the 15th.

Then, create a formula that adds 30 * the # of months (2nd column) to the Helper Date.

Then set the formula so that the DAY switches back to the date in the original date (not the 15th in the helper date).

So the actual formula is this:

=DATE(YEAR(Helper@row + (Months@row * 30)), MONTH((Helper@row + (Months@row * 30))), DAY(Date@row))

By forcing the calculation to use a date in the middle of the month, no matter whether you have a 28 day month or a 31 day month, the end result always gives you the month and year you're looking for, and all you have to do is plug the right day back in....

It seems like a complicated way to get there, but it works.

Cathy Fraser

Thanks MCorbin, in regards to the Helper date, do you setup a formula that auto calculates this for you and if so what is that formula?