Years calculation (2003)

[Karat]

Hi there
I want to put a date (day month year) on every column ... In one cell I have a date .. 1 June 2009.. In the next cell I put =a1+365. That works ok until you hit leap years. I have to do 100 years.
Is there a formula to do this ???? Because after 80 years it jumps to May.

Thanks

[wittigbg]

Probably a better way, but if date is in A1 formula in A2

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

and copy down

[Karat]

Thanks heaps

[HansV]

You can also do this without formula:
In the first cell, enter 1 June 2009.
In the cell below it, enter 1 June 2010.
Select these two cells, then fill down for another 98 cells by dragging the fill handle in the lower right corner.

[Jezza]

Just as an addition to Hans' answers you could use the little used function Edate. To do this you need the Analysis Toolpak installed.

In A1 type in 1/6/2008 then in A2 type =EDATE(A1,12) and then fill down.

Just another option <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[bosco_yip]

One more way, A2 entered formula and then fill down :

=EOMONTH(A1,11)+1

Regards
Bosco

[franciz]

A shorter keystroke

enter 1 Jun 2009
right click the cell and fill down
choose Fill Years

cheers, francis

[Karat]

Thanks everybody....

[chrisgreaves]

In teaching any Windows-based application, I tell my clients "There are 4 ways of doing everything", and "If you don't know all 4, you probably aren't using your computer efficiently",
but FIVE ways to fill succesive years?
You guys take the cake! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

[HansV]

> You guys take the cake!

Cake and Excel - see the thread starting at <post:=758,159>post 758,159</post:>

[chrisgreaves]

> Cake and Excel - see the thread starting at post 758,159

Crumbs!
And all these years i thought it was cookies!