Add Months to Date formula?
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!
Best Answers
-
You are missing a closing parenthesis on the first line - ISDATE([Payment Start Date]@row, - and you have an extra closing parenthesis at the very end. This should fix it:
=IF(AND(ISDATE([Payment Start Date]@row), ISNUMBER([Payment Term Months]@row)), DATE(YEAR([Payment Start Date]@row) + FLOOR((MONTH([Payment Start Date]@row)) / 12, 1), MOD(MONTH([Payment Start Date]@row) + [Payment Term Months]@row, 12), DAY([Payment Start Date]@row)), "")
-
My apologies... I did indeed neglect to include the issue of spanning years; this should fix that issue. I also discovered a couple of issues that could crop up if the result happened to fall in December that have now been fixed also. I have tested this and I can find no problems, but please let me know if I missed something.
=IF(AND(ISDATE([Payment Start Date]@row), ISNUMBER([Payment Term Months]@row)), DATE(YEAR([Payment Start Date]@row) + (FLOOR((MONTH([Payment Start Date]@row) + [Payment Term Months]@row - 1) / 12, 1)), IF(MOD(MONTH([Payment Start Date]@row) + [Payment Term Months]@row, 12) = 0, 12, MOD(MONTH([Payment Start Date]@row) + [Payment Term Months]@row, 12)), DAY([Payment Start Date]@row)), "")
Answers
-
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.
The average month length is 30.4375 days (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.
-
@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.
-
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)), "")
-
Thanks for this! I've entered your proposed formula with the result "#INCORRECT ARGUMENT SET". This is way beyond my formula knowledge, so I have no idea what may be wrong at this point. I did notice that my Month column was labeled "Payment Term (Months)"... the parenthesis may have been causing issues with the formula, so I removed the parenthesis, but that didn't seem to completely solve it. Any idea what might be wrong? I so appreciate your help!
-
You are missing a closing parenthesis on the first line - ISDATE([Payment Start Date]@row, - and you have an extra closing parenthesis at the very end. This should fix it:
=IF(AND(ISDATE([Payment Start Date]@row), ISNUMBER([Payment Term Months]@row)), DATE(YEAR([Payment Start Date]@row) + FLOOR((MONTH([Payment Start Date]@row)) / 12, 1), MOD(MONTH([Payment Start Date]@row) + [Payment Term Months]@row, 12), DAY([Payment Start Date]@row)), "")
-
Thank you for catching that! Now I'm getting #INVALID COLUMN VALUE
Does it matter that the Payment Start Date column is a "date" column and not text? When I changed the column type to text, the formula cell went blank. No error message, just blank. Oh, I see, using "ISDATE" works with date-type columns? Then, I still have the same question/suspicion: do I need to reformat the date somehow in a helper column to make this formula work? Does it instead need to be in a 01-Jan-22 type of format, similar to the original formula I was trying to use?
-
Could you please help me with basically the same, but with days? I have a date column, Initiation Date, and an interval column with a formula that returns 7 or 30 days, depending on another factor, and I want to add the two together, but when I do =([Initiation Date]@row ( example-8/10/23) + Interval I am getting 8/10/2030 instead of 9/20/2023. I've tried several different things but I can't get it to work.
-
@Carson Penticuff - Never mind! I got it to work!
-
The ISDATE() is checking to make sure there is actually a date entered in the column. [Payment Start Date] must be a date column for this to work. As far as specific formatting, it really doesn't matter. 1/1/23 vs Jan. 1 2023 etc etc are for visual representation only. Smartsheet treats them all the same in the background. The column you place this formula into must also be formatted as a date, as that is the expected return.
-
OH!! Ok, now it works! I had to formula column as just a text column for the formula. Once I changed it to a date formula, it worked!! Thank you so much for your help!
-
-
Do you have a suggestion for when the date range is over a year? i.e. adding 24 months to a date.
-
@SGWIN the above solution works for any number of months :)
-
I thought it would, but it gave me the same year.....been racking my brain trying to figure it out.
-
@SGWIN Interesting... I'm using the above solution for 24 months too... I just went back to check and you're right (unfortunately!)... it only cycles through the months and doesn't add to the years. Thank you for your comment, I wouldn't have caught this until it was already inserted into someone's contract incorrectly...
@Carson Penticuff Do you have any thoughts on how to update the years as months are added? (If you don't mind?) I believe you've been able to address this with the days to months. Can the formula be added to to update months to years?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
