Calculate next year in different cell

  • Thread starter Thread starter duketter
  • Start date Start date
D

duketter

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
 
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
 
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?
 
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?
 
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!
Click to expand...

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

--ron
 
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!
Click to expand...

=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))
 
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))
 
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))
Click to expand...

Good point -- I missed the "2007" specification.
--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))
Click to expand...

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