need to shift dates forward 1 year

J

joan

greetings!

I have an annual schedule that, after some editing, needs to have all dates
in a column moved forward 1 year. is there a way to accomplish this without
actually mannually re-entering the dates?

TIA
Joan
 
T

tjtjjtjt

Depending on your Date Format, you could Select the Column, and then do an
Edit|Replace.
Ex:
For: 1/01/2004
Replace: 2004
With: 2005
Make sure you select the column first, if you plan to use Replace All.

tj
 
D

Don Guillett

this should do it

Sub addyr()
For Each c In Selection
c.Value = DateSerial(Year(c) + 1, Month(c), Day(c))
Next
End Sub
 
D

Don Guillett

Let's modify a bit to preclude changing too much

Sub addyr()
For Each c In Selection
If IsDate(c) Then
c.Value = DateSerial(Year(c) + 1, Month(c), Day(c))
End If
Next
End Sub
 
B

Bill Martin

I have an annual schedule that, after some editing, needs to have all dates
in a column moved forward 1 year. is there a way to accomplish this without
actually mannually re-entering the dates?
Click to expand...

A third approach:

Assuming for example you have a date in column A and want the same date a
year from now in column B, then: B1=EDATE(A1,12)

Just copy that all the way down the B column and you've got it!

Bill -- (Remove KILLSPAM from my address to use it)
 
S

Shinecatcher

Hello,

Can you tell me how to put dates across columns automatically for a given
month?

--Shinecatcher
 
D

Dave Peterson

There's a little bit of a learning curve--but you'll be pretty effective after
just a little time (maybe an hour).

But you'll be finding new stuff all the time--remember that rightclicking on
almost anything in excel is your friend!

If you want to try the pivottable stuff, you may want to look at somee links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx


I've never created a pivot table. Is it easy?
Click to expand...
 
S

shinecatcher

Wow! Thanks for the great links! I'll give it a shot.

With great appreciation,
Shinecatcher

Dave Peterson said:
There's a little bit of a learning curve--but you'll be pretty effective after
just a little time (maybe an hour).

But you'll be finding new stuff all the time--remember that rightclicking on
almost anything in excel is your friend!

If you want to try the pivottable stuff, you may want to look at somee links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
Click to expand...
 
Top