Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next year in different cell
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 ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next year in different cell
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 ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next year in different cell
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next year in different cell
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 ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next year in different cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate value at the end of nth year using CAGR | Excel Worksheet Functions | |||
calculate Age Year Month & day | Excel Discussion (Misc queries) | |||
How do I crate a function for week of year + year in same cell. | Excel Worksheet Functions | |||
How do I calculate year vs. year sales in a percentage? | Excel Discussion (Misc queries) | |||
how to calculate $1000/year at 5% for 25 years showing each year | Setting up and Configuration of Excel |