Adding "Months" to a Date (Column)

I have a Column [Planned FPI] 01/02/23 that I want to ADD a # of Months [Estimated Duration (Months] to and I can't get the formula to return a Date.


Tags:

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    This formula will return the date you need:

    =DATE(YEAR(((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row), MONTH(((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row), DAY([Planned FPI]@row))

    Explanation

    Given...

    1 Year = 12 months

    1 Year = 365 days

    ..there are 365/12 days in a month.

    So 24 months from 11/9/2022 would be ((365/12)* 24 ) + (11/9/2022).

    Using the column names from the sheet, the expression is...

    ((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row

    However, 2024 is a leap year so this formula returns 11/8/2024 instead of 11/9/2024.

    You'll have to force the "9" from the "Planned FPI" date into the calculation to get 11/9/2024.

    DATE( YEAR(11/8/2024) , MONTH(11/8/2024) , DAY(11/9/2022) )

    Substitute...

    =DATE( YEAR( ((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row ) , MONTH( ((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row ) , DAY( [Planned FPI]@row ) )

  • Susan Swisher
    Susan Swisher ✭✭✭✭✭

    @Toufong Vang THIS WAS PERFECT! amazing.. THANK YOU

  • Hi Susan,

    This one is a little complicated but with the help of the MOD() function, we should be able to make this work. I am hoping the following function is formatted so you may just copy and paste it into your [Actual FPI] column but I am providing a pdf illustrating how this combination of functions work to help trouble shoot any issues you may have. 😊

    Function:

    =DATE(ROUNDDOWN(YEAR([Planned FPI]@row) + [Estimated Duration (Months)]@row / 12), MOD([Estimated Duration (Months)]@row, 12) + MONTH([Planned FPI]@row), DAY([Planned FPI]@row))



    I hope this achieves the outcome you are looking for!

    -Jessica

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!