@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?
Add Months to Date formula?
Answers
-
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)), "")
-
Thank you! You are amazing (in case no one has told you today)!
-
@Carson Penticuff I just now got around to incorporating the updates you posted the other day. It works! (I mean, I was able to copy it exactly this time!)
Thank you again for ALL of your help! What a complicated formula for a seemingly simple outcome! Amazing, really.
-
I'm just running into this, but found a solution that works for me regardless of the number of months you're adding. In my case, I am adding "Months Between Maintenance" to "Last Maintenance Date" to populate "Next Maintenance Date". My formula to calculate the "Next Maintenance Date" column is as follows:
=IF(MOD([Months Between Maintenance]@row, 12) = 0, DATE(YEAR([Last Maintenance Date]@row) + (ROUNDUP([Months Between Maintenance]@row / 12, 0)), MONTH([Last Maintenance Date]@row), DAY([Last Maintenance Date]@row)), IF(MONTH([Last Maintenance Date]@row) + [Months Between Maintenance]@row > 12, DATE(YEAR([Last Maintenance Date]@row) + (ROUNDUP([Months Between Maintenance]@row / 12, 0)), MONTH([Last Maintenance Date]@row) + MOD([Months Between Maintenance]@row, 12) - 12, DAY([Last Maintenance Date]@row)), DATE(YEAR([Last Maintenance Date]@row), MONTH([Last Maintenance Date]@row) + [Months Between Maintenance]@row, DAY([Last Maintenance Date]@row))))
Hope this helps someone down the line!:)
-
Thanks for this!
-
I realized that crafting this formula in Smartsheet isn't straightforward, so I dedicated some time today to develop a comprehensive solution that includes leap years among other considerations. Here it is:
=DATE(YEAR(Date@row) + FLOOR((MONTH(Date@row) + monthsToAdd@row - 1) / 12, 1), MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1, MIN(DAY(Date@row), IF(MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1 = 2, IF(IF(OR(AND(MOD(YEAR(Date@row), 4) = 0, MOD(YEAR(Date@row), 100) <> 0), MOD(YEAR(Date@row), 400) = 0), 1, 0) = 1, 29, 28), IF(OR(MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1 = 4, MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1 = 6, MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1 = 9, MOD(MONTH(Date@row) + monthsToAdd@row - 1, 12) + 1 = 11), 30, 31))))
You need a column "Date" and a column "monthsToAdd" and you can copy and paste the formula above.
-
@Carson Penticuff Hello Carson.
Can you help me fix this formula to account for the spanning of years?
=IF(AND([Copper Report]@row = 1, [CR Frequency (Months)]@row = 3), DATE(YEAR([CR Due Date]@row), MONTH([CR Due Date]@row) + 3, DAY([CR Due Date]@row)), "")
-
@Ami Veltrie Give this a try:
=IF(AND([Copper Report]@row = 1, [CR Frequency (Months)]@row = 3), DATE(IF(MONTH([CR Due Date]@row) >= 10, YEAR([CR Due Date]@row) + 1, YEAR([CR Due Date]@row)), IF(MONTH([CR Due Date]@row) >= 10, MONTH([CR Due Date]@row) - 9, MONTH([CR Due Date]@row) + 3), DAY([CR Due Date]@row)), "")
-
@Carson Penticuff That worked! You are appreciated. Thank you!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!