Add Months to Date formula?

vdemattei

I've read through a lot of the help topics and others' questions, but haven't been able to find a simple solution to my issue - which I think is fairly simple.

I'm trying to add a certain number (Varying) of months to a start date to produce an end date.

I have a start date column (which is a date column), a column that has the number of months (they're drop down options), and then I'm trying to produce the end date after so many months have passed.

I found this solution/excel formula on a website:

It appears that it should work, but it does not work when I try it in Smartsheet. I believe it may be because my date format isn't what's needed for this type of formula. So now I'm tasked with trying to re-format the date column (using a helper column) into a format that will allow me to use the above formula. Can someone help me with this?

Thank you!

Carson Penticuff

Slight edit added...

Dealing with months is somewhat tricky.

"How" do you want to add the months? Here are a few examples of ways to deal with months:

With a start date of 01/31/2024 and adding one month... simply incrementing the month will give you 02/31/2024, which is obviously not possible.

月平均长度是30.4375天(factoring in leap years as well). This can be rounded down to 30 days. A start date of 01/31/2024 and adding 40 months (40 x 30 = 1200) will return 04/15/2027 as a result. Mathematically this makes sense, logically you would expect the result to land in May. I believe Smartsheet essentially does this in the background so it is the easiest to implement.

Going back to our first example (Starting on 01/31/2024 and adding one month), we could approach this a couple of different ways. We can say that any result that lands on an "impossible" day just gets "pulled back" to the last valid day of the month. So 01/31/2024 + 1 month would return 02/29/2024 (leap year).

The other option is to have it roll over to the first day of the following month. 01/31/2024 + 1 month = 03/01/2024.

How you would prefer to handle the result will dictate how to approach this.

vdemattei

@Carson Penticuff, thanks for your thoughtful response! You've brought up an interesting point. It appears in the excel formula I'd like to use, it simply adds to the month number, but will increase the year after hitting the 12th month... But I didn't consider the day implications - if my start date is on the 31st, you can't simply add months as not all months have 31 days.

I agree with your preference. If the above instance occurs, than we should "pull back" to the last valid day of said month. I think my simple formula just got much more complicated! Is this something you can help with?

I will also have to discuss this with my client and see if there are more specific payment terms that I should be incorporating, but I'd like to address it as you've suggested in the meantime.

Carson Penticuff

I was working on this before I saw your comment. This will basically add the number of months, and if the result is an invalid day, will add the number of additional days, throwing it into the next month. This is probably the "easiest" solution to implement. Pulling the date back to the last day of the previous month will be a little harder. You can take this for a test drive, and we can adjust if needed.

=IF(AND(ISDATE([Start Date]@row), ISNUMBER([Months]@Row)), DATE(YEAR([Start Date]@row) + FLOOR(([Months]@row + MONTH([Start Date]@row)) / 12, 1), MOD(MONTH([Start Date]@row) + [Months]@row, 12), DAY([Start Date]@row)), "")