Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Add Months to Date formula?

2»

Answers

  • Community Champion
    Answer ✓

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

  • ✭✭

    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.


  • ✭✭✭✭✭✭
    edited 07/03/24

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

  • Community Champion

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions