Add number of months onto a date

Saylor

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

Saylor,

 

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.

 

Craig

 

 

 

 

Saylor

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

You are welcome.

 

Craig