Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Add number of months onto a date

Saylor ✭✭✭
edited 12/09/19 in Archived 2016 Posts

I have a sheet that summarizes all of our projects.  I want to add the projected term of the project (in months) to the kick off date of the project.  Here is what I came up with but it is not working:


DATE(MONTH([Kick Off]1) + [Project Term]1)


Is there a formula that I can reference the date on one cell and add...say "6 months" to that date?



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Short answer: DATE takes three arguments. You have only one.


    To clarify,

    [Kick Off] is date type column.

    [Project Term] is a text/number type column.


    You want to add the [Project Term] (a number in months) to the [Kick Off] date and this will be displayed in another date type column.


    =[Kick Off]1 + [Project Term]1 * 30


    will get you close. That adds 30 days times the number of months to the Kick Off date.

    It will be off the farther out you go (there are 91 days per quarter, not 90)

    30.33 will get you closer.

    This will also work when the Project Term results in a date next year.


    If you are set on 6 months from now must be the same calendar date (2/17 +  6 months is 8/17), then you need to account for the year change.


    The formula for the next 12 months would be:


    =IF(MONTH([Kick Off]1) + [Project Term]1 > 12, DATE(YEAR([Kick Off]1) + 1, MONTH([Kick Off]1) + [Project Term]1 - 12, DAY([Kick Off]1)), DATE(YEAR([Kick Off]1), MONTH([Kick Off]1) + [Project Term]1, DAY([Kick Off]1)))


    pseudo code

    if the kick off month + project term is more than 12

    then the date is next year

    otherwise it is this year.

    date is DATE(year,month,day)


    If your project term's are longer than 12 months, there's another level of complexity to determine if you are going beyond more than one year.







  • Saylor
    Saylor ✭✭✭
    edited 04/07/16

    thank you so much for the feedback.  I am going to try this in the morning.  I believe I know where to find the examples for the additional level of complexity "IF" coding if the projects go past 12 months.


    Thanks again and have a great night.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You are welcome.



This discussion has been closed.