# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] how do i add 1 year to a date formatted cell?

## rogerm

I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5 and
10 and place the new dates in other cells.  Adding 365 doesn't work because
of leap years.  Any suggestions?

----------


## Peo Sjoblom

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

--
Regards,

Peo Sjoblom

(No private emails please)


"rogerm" <rogerm@discussions.microsoft.com> wrote in message
news:2DC73F9B-FDED-4EEC-B441-7811DE8EA883@microsoft.com...
>I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5
>and
> 10 and place the new dates in other cells.  Adding 365 doesn't work
> because
> of leap years.  Any suggestions?

----------


## William

Hi rogerm

This formula will add one year to the date in cell A1. Replace the "+1" in
the formula with the number of years you want to add.

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


XL2003
Regards

William
willwest22@yahoo.com


"Rogers" <rogerm@discussions.microsoft.com> wrote in message
news:2DC73F9B-FDED-4EEC-B441-7811DE8EA883@microsoft.com...
>I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5
>and
> 10 and place the new dates in other cells.  Adding 365 doesn't work
> because
> of leap years.  Any suggestions?

----------


## Gary's Student

Let's say the start date is in cell A1.  In another cell enter:

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

This will give the date 1 year later.  You can change the +1 to +5 to make
it 5 years, etc.

Have a good day
--
Gary's Student


"rogerm" wrote:

> I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5 and
> 10 and place the new dates in other cells.  Adding 365 doesn't work because
> of leap years.  Any suggestions?

----------


## rogerm

Thanks for the help!  It works just like I need it to.

"rogerm" wrote:

> I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5 and
> 10 and place the new dates in other cells.  Adding 365 doesn't work because
> of leap years.  Any suggestions?

----------


## Dana DeLouis

Just want to point out a difference.  If A1 has the date 2/29/2004, then
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
returns 3/1/2005
and
=EDATE (A1,12)
returns
2/28/2005

HTH
--
Dana DeLouis
Win XP & Office 2003


"rogerm" <rogerm@discussions.microsoft.com> wrote in message
news:AE106FC9-D990-42FB-9DB3-F37C2A6F8663@microsoft.com...
> Thanks for the help!  It works just like I need it to.
>
> "rogerm" wrote:
>
>> I would like to increase the year in a MM/DD/YYYY formated cell by 1, 2,5
>> and
>> 10 and place the new dates in other cells.  Adding 365 doesn't work
>> because
>> of leap years.  Any suggestions?

----------


## Hal Whitis

When I use the formula  =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))  , it works perfectly as long as there is an existing date in the reference cell.  However, if the reference cell is blank, such as in a template waiting to be filled out, the formula cell is showing a goofy date, such as Dec-00.  Suggestions on how to have the formula cell completely blank until a date is entered into the reference cell?

----------


## Sam Capricci

Hal, first, welcome to the forum!  This post is almost 14 years old.  Posting on another thread with a question is a violation of forum rule #4.  If you have a question start your own post AND you can include a link to this post if you feel it is relevant to yours.
Again welcome to the forum!   :Smilie:

----------

