Calculate next year in different cell

Excel 2007 - I have a specific date in cell A1 for example. A1 - 3/31/2009.
I then want in cell G1 the exact next year calculated so for example cell G1
would show 3/31/2010. Then in cell H1 it would show 3/31/2011, I1 it would
show 3/31/2012.

The date in cell A1 changes so that is why I need the formula for this. I
have tried just putting the following formula in cell G1: =A1+365. However,
when I hit leap year it screws up the days and goes to 3/30/2012, 3/29/2016
etc.

How can I get this to calculate exactly the next year and input the data?

Thanks!

In G1:

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

In H1, use the same formula, referencing G1 instead of A1, and then copy across.

HTH,
Bernie
MS Excel MVP


"duketter" wrote in message
news
Excel 2007 - I have a specific date in cell A1 for example. A1 - 3/31/2009.
I then want in cell G1 the exact next year calculated so for example cell G1
would show 3/31/2010. Then in cell H1 it would show 3/31/2011, I1 it would
show 3/31/2012.

The date in cell A1 changes so that is why I need the formula for this. I
have tried just putting the following formula in cell G1: =A1+365. However,
when I hit leap year it screws up the days and goes to 3/30/2012, 3/29/2016
etc.

How can I get this to calculate exactly the next year and input the data?

Thanks!

when I hit leap year it screws up the days

If the start date was 2/29/2008 what dates would you expect as you copy
across?

Would one year from 2/29/2008/ be 2/28/2009 or 3/1/2009?

--
Biff
Microsoft Excel MVP


"duketter" wrote in message
news
Excel 2007 - I have a specific date in cell A1 for example. A1 -
3/31/2009.
I then want in cell G1 the exact next year calculated so for example cell
G1
would show 3/31/2010. Then in cell H1 it would show 3/31/2011, I1 it
would
show 3/31/2012.

The date in cell A1 changes so that is why I need the formula for this. I
have tried just putting the following formula in cell G1: =A1+365.
However,
when I hit leap year it screws up the days and goes to 3/30/2012,
3/29/2016
etc.

How can I get this to calculate exactly the next year and input the data?

Thanks!

On Wed, 13 May 2009 11:26:02 -0700, duketter
wrote:

Excel 2007 - I have a specific date in cell A1 for example. A1 - 3/31/2009.
I then want in cell G1 the exact next year calculated so for example cell G1
would show 3/31/2010. Then in cell H1 it would show 3/31/2011, I1 it would
show 3/31/2012.

The date in cell A1 changes so that is why I need the formula for this. I
have tried just putting the following formula in cell G1: =A1+365. However,
when I hit leap year it screws up the days and goes to 3/30/2012, 3/29/2016
etc.

How can I get this to calculate exactly the next year and input the data?

Thanks!

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

--ron

Since the OP mentioned using Excel 2007 I'd use:

A1 = base date

Entered in B1 and copied across:

=EDATE($A1,12*COLUMNS($B1:B1))

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
news
On Wed, 13 May 2009 11:26:02 -0700, duketter
wrote:

Excel 2007 - I have a specific date in cell A1 for example. A1 -
3/31/2009.
I then want in cell G1 the exact next year calculated so for example cell
G1
would show 3/31/2010. Then in cell H1 it would show 3/31/2011, I1 it
would
show 3/31/2012.

The date in cell A1 changes so that is why I need the formula for this. I
have tried just putting the following formula in cell G1: =A1+365.
However,
when I hit leap year it screws up the days and goes to 3/30/2012,
3/29/2016
etc.

How can I get this to calculate exactly the next year and input the data?

Thanks!

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

--ron

On Wed, 13 May 2009 22:49:32 -0400, "T. Valko" wrote:

Since the OP mentioned using Excel 2007 I'd use:

A1 = base date

Entered in B1 and copied across:

=EDATE($A1,12*COLUMNS($B1:B1))

Good point -- I missed the "2007" specification.
--ron