Add a month

  • Thread starter Thread starter Newbie
  • Start date Start date
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
 
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 **
 
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
(e-mail address removed)

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/
 
Thanks everyone. Your formulas are going to save lot of
time for me from typing months and years in cells. Thanks.
 
Hi Paul,

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

Kind regards
 
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
(e-mail address removed)

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/
 
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)
 
Back
Top