Help a newbie: Change year in dates?

[listener]

I created a spreadseet last year that I want to reuse each year.
Column A, cells 1 through 365 contain dates thus:

01/01/03
01/02/03
01/03/03
etc.
..
..
all the way to

12/31/03


I've made a copy of the spreadsheet and cleared out the data. I now
want to use it for this year. How do I change the xx/xx /03's to
xx/xx/04's globally.


Thanks.

 

[Arvi Laanemets]

Hi

Create a named range p.e. SpreadsheetYear, like
SpreadsheetYear=2004
or
SpreadsheetYear=SetUp!$B$1
where the year is entered into cell B1 on sheet SetUp

In your table, enter into cell A1 the formula
=IF(YEAR(DATE(SpreadsheetYear,1,ROW(A1)))=SpreadsheetYear,DATE(SpreadsheetYe
ar,1,ROW(A1)),"")
and copy it down until A366 (last included to cover leap years)

Now, whenever you want to change the year the spreadsheet is bound, change
the year stored in SetUp sheet or in named range.

 

[Peo Sjoblom]

put 365 in an empty cell, copy, select A1:A365, do edit>paste special and
select add
note that 2004 is a leap year so A365 will be 12/30/04 so you need to add
one more date
also note when you paste the addition the dates will turn to date serial
numbers so you need to select the range
and format as dates again.

Having said that it is very easy to make a date column that will adapt
easier
in A1 put the first date, in A2 put

=A1+1


then copy down, next time you only have to change the first date and if you
want you can in cell A366 put this formula

=IF(DAY(DATE(YEAR(A1),3,0))=29, A366+1,"")

that way your dates will adapt to leap years

 

[David McRitchie]

The year 2004 is a leap year. so you want A1:A366

You could place 01/01/2004 in A1 then
in the name box to the left of the formula bar enter A1:A366
then Edit, Fill, Fill Series

use of fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

use of shortcuts
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

 

[David McRitchie]

If by spam trap you mean automatic reporting of
spammers (blackhole) then it has no business being your
reply email address. If it is a valid email address but
not yours it also has no business being there.

 

[Arvi Laanemets]

Hi

It's simply a specially created real e-mail address with rules, that all
incoming mails are immediately deleted


Arvi Laanemets

 

[listener]

Thanks to everyone for their suggestions and help.

It was very much appreciated!