Adding Months to Dates to create new value

[Guest]

I have a column that contains various dates (xx/xx/xxxx) In a column next to
this one I would like to add various # months to come up with a new date

8/1/2006 + 6months = 2/1/2007

Is this possible with formula?

Thanks

 

[David Biddulph]

=(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))) will give the answer you asked for in
that case, but the question in this type of problem is what you want as an
answer when you add 6 months to a date like 30 Aug 06.

 

[Ron Rosenfeld]

I have a column that contains various dates (xx/xx/xxxx) In a column next to
this one I would like to add various # months to come up with a new date

8/1/2006 + 6months = 2/1/2007

Is this possible with formula?

Thanks

You can use the EDATE function.

With 8/1/2006 in A1, =EDATE(A1,6)

From HELP:

Returns the serial number that represents the date that is the indicated number
of months before or after a specified date (the start_date). Use EDATE to
calculate maturity dates or due dates that fall on the same day of the month as
the date of issue.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
Syntax

EDATE(start_date,months)

==========================================

Or, without the ATP:

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

will return, for example, 2/29/2008 with 8/31/2007 in A1.

--ron

 

[Ron Rosenfeld]

You can use the EDATE function.

With 8/1/2006 in A1, =EDATE(A1,6)

From HELP:

Returns the serial number that represents the date that is the indicated number
of months before or after a specified date (the start_date). Use EDATE to
calculate maturity dates or due dates that fall on the same day of the month as
the date of issue.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
Syntax

EDATE(start_date,months)

==========================================

Or, without the ATP:

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

will return, for example, 2/29/2008 with 8/31/2007 in A1.

--ron

I forgot to mention that this formula I first saw posted by Bob Phillips.

If you have the following:

A1: Date
A2: Number of months

Then you can try this formula:

=MIN(DATE(YEAR(A1),MONTH(A1)+A2*2+1-ROW(INDIRECT(A2&":"&A2+1)),DAY(A1)*{1,0}))


--ron