Add 6 months to a date

[M.Siler]

I've looked and can't seem to determine somethat should be simple. I've got
a date 3/31/2006 and I want to add 6 months so the resulting answer should
be 9/30/2006. I know I can use Month on the original date and get 3 then
add the number of months I want to get 9 (september), but how do I get it to
give me the last day of the month given March 31, 2000 plus 6 months should
be Sept. 30, 2006.

Also, what if I want to add 18 months to 3/31/2006... I'd want the answer to
be 9/30/2007.

Thanks!

PS - I'm unsing Excel 2003

 

[Dirk Van de moortel]

I've looked and can't seem to determine somethat should be simple. I've got
a date 3/31/2006 and I want to add 6 months so the resulting answer should
be 9/30/2006. I know I can use Month on the original date and get 3 then
add the number of months I want to get 9 (september), but how do I get it to
give me the last day of the month given March 31, 2000 plus 6 months should
be Sept. 30, 2006.

Also, what if I want to add 18 months to 3/31/2006... I'd want the answer to
be 9/30/2007.

It's not clear from your question whether you always and
only have *last* days of the month to which you want to
add N months.
If that is indeed the case, then you can simply use
= A1 + N*31 - DAY( A1 + N*31 )
where N is the number of months.
The first part brings you a month too far and the second
part brings you back to the last day of the month before.
This works for N between 0 and at least 25.

Dirk Vdm

 

[Bernard Liengme]

The trick to getting the last day of the month is to ask for day 0 of the
next month
With start date in A1, months to add in B1, use
=DATE(YEAR(A1),MONTH(A1+B1+1,0)
best wishes

 

[SteveG]

You can use the EOMONTH function but you need to have the Analysi
ToolPak installed. To activate the Analysis ToolPak go t
Tools>Addins>select the checkbox for the Analysis ToolPak. Click OK.

If your date is in A1 then,

=EOMONTH(A1,6)

Just change the 6 to whatever number of months you want. You can us
this in reverse as well. Say you wanted the date 6 months previous
use a negative number instead.


HTH

Stev

 

[Bernard Liengme]

Typo, should be =DATE(YEAR(A1),MONTH(A1+B1+1),0)

 

[Bill Ridgeway]

Correction to the correction. This one works-

=DATE(YEAR(A1),MONTH(A1)+B1,0)

Regards.

Bill Ridgeway
Computer Solutions

 

[Bill Ridgeway]

Correction to the correction to the correction. This one returns the last
day of the month in which 1 months is added to the start date -

=DATE(YEAR(A1),MONTH(A1)+B1+1,0)

which is closer to what was wanted in the first place than my last
suggestion. Apologies.

Regards.

Bill Ridgeway
Computer Solutions

 

[M.Siler]

Nice function... I like, but don't totally fulfill the goal.

If I have 3/31/2006 and want to add 11 months using EOMONTH I get 2/28/2007
which is perfect!! The problem I was having was if I took 11 months at 30
days per month for a total of 330 days and added it to 3/31/2006 it would
give me 2/24/2007 which isn't what you'd expect. But if I have a start date
of 3/20/2006 and I add 11 months the EOMONTH still gives me 2/28/2007 when I
would expect 2/20/2007.

Summary:

3/31/2006 Plus 11 months should result in 2/28/2006
3/20/2006 Plus 11 months should result in 2/20/2006

 

[Dave Peterson]

One way:

=DATE(YEAR(A1),MONTH(A1)+11,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+12,0))))

 

[SteveG]

Try this instead,

=EDATE(A1,11)

Regards,

Steve

 

[Bill Ridgeway]

Just to round up (for now) the question on how to manipulate dates, here are
some formulae (which assume the root date is in A1) for advancing dates. It
started as an exercise to try to understand the subject - hence the number
of variations. I hope it will be of use.

1 day
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

1 month
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

1 year
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

1 year, 1 month, 1 day
=DATE(YEAR(A1)+1,MONTH(A1)+1,DAY(A1)+1)

1 year, 1 month
=DATE(YEAR(A1)+1,MONTH(A1)+1,DAY(A1))

First day of following month
=DATE(YEAR(A1),MONTH(A1)+1,1)

Last day of current month
=DATE(YEAR(A1),MONTH(A1)+1,0)

Last day of following month
=DATE(YEAR(A1),MONTH(A1)+1+1,0)

Tenth day of following month
=DATE(YEAR(A1),MONTH(A1)+1,10)

Bill Ridgeway
Computer Solutions

 

[M.Siler]

Perfect!! Exactly what I was hoping for. Thank you everyone for you help.