Formula for Adding Months

I am having a hard time with finding a formula for adding months, rather than days. I need to be able to input a date in one date column, and have it calculate 10 months out in another date column.

Best Answer

  • Jason P
    Jason P ✭✭✭✭✭
    Answer ✓

    Hi @Natasha W.

    Returning to community, I had the same for 6 months in my early Smartsheet day's and was assisted, for you I changed the 6 to a 10.. This accounts for leap years too

    Save to the Date Cell you want the result in and convert to column formula to capture all row entries as they come in, where there is no date in the [Date Column] the result cell remains blank.

    =IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row) + FLOOR((MONTH([Date Column]@row) + 10 - 1) / 12, 1), MOD(MONTH([Date Column]@row) + 10 - 1, 12) + 1, DAY([Date Column]@row)))

    Good luck.

    Cheers.

Answers

  • @Natasha W.

    Try this:
    =DATE(YEAR([Original Date Column Name]@row), MONTH([Original Date Column Name]@row) + 10, DAY([Original Date Column Name]@row))

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭

    @Natasha W.

    If it's never going to be anything other than 10 months, you can use the below. If 10 becomes a variable it would need some changes. Note this also takes the exact day so if you add 10 months to a date that the day is 31 but the 10th month stops at 28, it might have an error so you'd need to modify the day portion to catch that.

    =DATE(IF(MONTH([Date1]@row) + 10 > 12, YEAR([Date1]@row) + 1, YEAR([Date1]@row)), IF(MONTH([Date1]@row) + 10 > 12, MONTH([Date1]@row) + 10 - 12, MONTH([Date1]@row) + 10), DAY([Date1]@row))

    Certified Platinum Partner

    PrimeConsulting.com

  • Jason P
    Jason P ✭✭✭✭✭
    Answer ✓

    Hi @Natasha W.

    Returning to community, I had the same for 6 months in my early Smartsheet day's and was assisted, for you I changed the 6 to a 10.. This accounts for leap years too

    Save to the Date Cell you want the result in and convert to column formula to capture all row entries as they come in, where there is no date in the [Date Column] the result cell remains blank.

    =IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row) + FLOOR((MONTH([Date Column]@row) + 10 - 1) / 12, 1), MOD(MONTH([Date Column]@row) + 10 - 1, 12) + 1, DAY([Date Column]@row)))

    Good luck.

    Cheers.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!