Add a month

N

Newbie

Hi All,

I have 1/4/04 in column A1. I want to write a formula in
Column B1 to add one month to the date in A1. In A2 I want
to add a year to A1. Result would show: B1=1/5/04 and
A2=1/4/05. How do I do this?

Your help is appreciated. Thank you

Regards
 
H

Harald Staff

Hi Regards

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

Paul B

Newbie, try this in B1 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
and this in A2 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Your results are showing you added a day to A1 and not a month?
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
N

Norman Harker

Hi Newbie!

B1:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
A2:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

But if A1 day of month could be >=29 then the following formula might
be better especially if you are going to copy it across other columns:

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0))))

It returns the same day in the next month unless that day number does
not exist in which case it returns the last day. EDATE (an Analysis
ToolPak function) uses the same algorithm but doesn't facilitate
copying down or across.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
N

Newbie

Thanks everyone. Your formulas are going to save lot of
time for me from typing months and years in cells. Thanks.
 
N

Newbie

Hi Paul,

Thanks for the reply. The formula works fine. For some
strange reasons we write dd/mm/yy in UK!

Kind regards
 
N

Norman Harker

Hi Newbie!

Thanks for thanks. From your "Your formulas are going to save lot of
time for me from typing months and years in cells" I'd suggest that
you use the copy down and across variant for adding months:

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0))))

There is a general form that allows adjustment by any number of
months:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+AddMons+1,0))))
Where AddMons is a named cell that contains the number of months to be
added each time.

Alternatively, there a shorter version by Peter Dorigo:

=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
2

2rrs

Newbie said:
Hi All,

I have 1/4/04 in column A1. I want to write a formula in
Column B1 to add one month to the date in A1. In A2 I want
to add a year to A1. Result would show: B1=1/5/04 and
A2=1/4/05. How do I do this?

Your help is appreciated. Thank you

Regards
Click to expand...

This will also work, but req the Analysis ToolPack be available:

B1, =EDATE(A1,1)
A2, =EEDATE(A1,12)
 
Top