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
- 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!
