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

  • Hi all

    I have tried using @Toufong Vang's formula but I'm not sure where I'm going wrong. I want to add the number of months from 4 columns to a date (Start Date). The answer I'm getting is 01/03/2016 rather than 02/08/2020. Any guidance would be appreciated.

    Thanks

    Mark

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 03/08/24

    Hi, @Mark McGrath, my approach above does not work consistently. The following will work as intended. Give it a try. (Replace the temporary column names--COL1, COL2, ect.--with yours.)

    =DATE(IF((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) >= 12, INT((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) / 12) + YEAR([Start Date]@row), YEAR([Start Date]@row)), MOD((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row), 12), DAY([Start Date]@row))

    Here's an explanation of this approach.

    The new expiry date is Z months away. Which can be expressed as:

    MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row

    That is to say...

    Z = (MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row)

    Using the DATE() function...

    DATE(year , month , day)

    or...

    DATE( year
        , month
        , day
        )
    

    year = IF( Z >= 12, INT(Z/12) + YEAR([Start Date]@row), YEAR([Start Date]@row))

    month = MOD(Z,12)

    day = DAY([Start Date]@row)

    ...or...

    DATE( IF( Z >= 12, INT(Z/12) + YEAR([Start Date]@row), YEAR([Start Date]@row))
        , MOD(Z, 12)
        , DAY([Start Date]@row)
        )
    

    ...substitute for Z to complete...

    DATE( IF((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) >= 12, INT((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) / 12) + YEAR([Start Date]@row), YEAR([Start Date]@row))
        , MOD((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row), 12)
        , DAY([Start Date]@row)
        )
    
  • Amazing @Toufong Vang, thanks so much! I really appreciate it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!