| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summary sheet showing holiday dates taken | Excel Worksheet Functions | |||
| Want cell ref. to change after sort in other sheet | Excel Discussion (Misc queries) | |||
| Macro to change data in a sheet | Excel Discussion (Misc queries) | |||
| Change scales from reference cells value for a chart in a separate sheet | Charts and Charting in Excel | |||
| when i move or copy a sheet, dates change by one day?? | Excel Discussion (Misc queries) | |||
Linear Mode
