Is there a way to change all dates in a spread sheet
 

I have a spread sheet with dates for the whole year of 2006. Exmaple I have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

editreplace0607

--
Don Guillett
SalesAid Software

"delete automatically" wrote
in message ...
I have a spread sheet with dates for the whole year of 2006. Exmaple I have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

or maybe
replace
/06
/07

--
Don Guillett
SalesAid Software

"delete automatically" wrote
in message ...
I have a spread sheet with dates for the whole year of 2006. Exmaple I have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

Excel stores the dates as 1/1/2006, so it won't find any instances of "/06"
to change. "\2006" and "/2007" do work.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Don Guillett" wrote in message
...
or maybe
replace
/06
/07

--
Don Guillett
SalesAid Software

"delete automatically"
wrote in message
...
I have a spread sheet with dates for the whole year of 2006. Exmaple I
have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

Try

Sub NextYear()
intRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To intRows
oldDate = ActiveSheet.Cells(i, 1).Value
If IsDate(oldDate) Then
newDate = DateAdd("yyyy", 1, oldDate)
ActiveSheet.Cells(i, 1).Value = newDate
End If
Next
End Sub

* will not account for leap years.
--
Eric Travers


"delete automatically" wrote:

I have a spread sheet with dates for the whole year of 2006. Exmaple I have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

or using what Jon posted

Sub Macro13()'recorded
'
' Macro13 Macro
' Macro recorded 12/22/2006 by Don Guillett
'

'
Cells.Replace what:="/2006", Replacement:="/2007", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


--
Don Guillett
SalesAid Software

"TraversE" wrote in message
...
Try

Sub NextYear()
intRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To intRows
oldDate = ActiveSheet.Cells(i, 1).Value
If IsDate(oldDate) Then
newDate = DateAdd("yyyy", 1, oldDate)
ActiveSheet.Cells(i, 1).Value = newDate
End If
Next
End Sub

* will not account for leap years.
--
Eric Travers


"delete automatically" wrote:

I have a spread sheet with dates for the whole year of 2006. Exmaple I
have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of dates.

Is there a way to change all dates in a spread sheet
 

Yeah, I guess you can use a macro to automate a simple find & replace.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Don Guillett" wrote in message
...
or using what Jon posted

Sub Macro13()'recorded
'
' Macro13 Macro
' Macro recorded 12/22/2006 by Don Guillett
'

'
Cells.Replace what:="/2006", Replacement:="/2007", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


--
Don Guillett
SalesAid Software

"TraversE" wrote in message
...
Try

Sub NextYear()
intRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To intRows
oldDate = ActiveSheet.Cells(i, 1).Value
If IsDate(oldDate) Then
newDate = DateAdd("yyyy", 1, oldDate)
ActiveSheet.Cells(i, 1).Value = newDate
End If
Next
End Sub

* will not account for leap years.
--
Eric Travers


"delete automatically" wrote:

I have a spread sheet with dates for the whole year of 2006. Exmaple I
have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have
other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of
dates.

Is there a way to change all dates in a spread sheet
 

Jon, I agree. Remember at the start of the thread I recommended replace.

--
Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...
Yeah, I guess you can use a macro to automate a simple find & replace.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Don Guillett" wrote in message
...
or using what Jon posted

Sub Macro13()'recorded
'
' Macro13 Macro
' Macro recorded 12/22/2006 by Don Guillett
'

'
Cells.Replace what:="/2006", Replacement:="/2007", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


--
Don Guillett
SalesAid Software

"TraversE" wrote in message
...
Try

Sub NextYear()
intRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To intRows
oldDate = ActiveSheet.Cells(i, 1).Value
If IsDate(oldDate) Then
newDate = DateAdd("yyyy", 1, oldDate)
ActiveSheet.Cells(i, 1).Value = newDate
End If
Next
End Sub

* will not account for leap years.
--
Eric Travers


"delete automatically" wrote:

I have a spread sheet with dates for the whole year of 2006. Exmaple I
have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have
other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of
dates.

Is there a way to change all dates in a spread sheet
 

I remember, so I was surprised you bothered with a VBA approach. Not that
there's anything wrong with that.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Don Guillett" wrote in message
...
Jon, I agree. Remember at the start of the thread I recommended replace.

--
Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...
Yeah, I guess you can use a macro to automate a simple find & replace.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Don Guillett" wrote in message
...
or using what Jon posted

Sub Macro13()'recorded
'
' Macro13 Macro
' Macro recorded 12/22/2006 by Don Guillett
'

'
Cells.Replace what:="/2006", Replacement:="/2007", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


--
Don Guillett
SalesAid Software

"TraversE" wrote in message
...
Try

Sub NextYear()
intRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To intRows
oldDate = ActiveSheet.Cells(i, 1).Value
If IsDate(oldDate) Then
newDate = DateAdd("yyyy", 1, oldDate)
ActiveSheet.Cells(i, 1).Value = newDate
End If
Next
End Sub

* will not account for leap years.
--
Eric Travers


"delete automatically" wrote:

I have a spread sheet with dates for the whole year of 2006. Exmaple I
have
1/1/06 thru 1/15/06 in the first 14 rows. Then rows 15 thru 20 have
other
data. Then start with 1/16/06 thru 1/29/06 then more data. How can I
changed
the dates to the right dates of 2007 without gong to each block of
dates.