Add Months to Date formula?

Carson Penticuff

@SGWIN @vdemattei

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)), "")

SGWIN

Thank you! You are amazing (in case no one has told you today)!

vdemattei

@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.

brianschmidt

https://community.smartsheet.com/discussion/comment/393505#Comment_393505

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

vdemattei

https://community.smartsheet.com/discussion/comment/411037#Comment_411037

Thanks for this!

VirgilT

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.

Ami Veltrie

https://community.smartsheet.com/discussion/comment/393510#Comment_393510

@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)), "")

Carson Penticuff

@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)), "")

Ami Veltrie

@Carson Penticuff That worked! You are appreciated. Thank you!!!