|  | Change date format for existing sheet? (XL2K+XL2003) | | |
| | | MitchellWMA |  |
|  Posted: Mon Jul 07, 2008 6:18 pm Post subject: Change date format for existing sheet? (XL2K+XL2003) |  |
Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help.  D |
| |
| | | Beege | |
| | Posted: Mon Jul 07, 2008 6:18 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
| |  | |
Mitchell,
I don't see why you need all the extra information (e.g., 1-30, 1-2 It
seems the intent is to include the whole month, like:
April 2007
May 2007
June 2007
Format the cells to MMMM yyyy
Fill in one, drag down as needed...
No help?
Beege
MitchellWMA wrote:
| Quote: | Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help. D |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Mon Jul 07, 2008 6:18 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
| | | |
This macro should do what you want (see note at after the code). Right-click
the tab for the worksheet with your dates on it and click "View Code" from
the popup menu that appears. When you do this, you will be taken into the
VBA editor and the code window for that worksheet will be automatically
displayed. Copy/Paste the following code into that code window...
Sub ConvertDateFormats()
Const YearValue As Long = 2007
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue
End If
Next
End Sub
Note: Change the values assigned in the Const statements at the beginning of
the code to the year for your dates and the range of cells containing your
dates. Now, back on your worksheet, press Alt+F8, select ConvertDateFormats
from the lists and click the Run button. Your dates should all change to the
new values you asked for (this is a permanent change, not simply a cell
format).
Rick
"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message
news:de629d4a-e66b-4659-bbb6-c9513fe812f7@z72g2000hsb.googlegroups.com...
| Quote: | Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help. D |
|
| |
| | | Pete_UK | |
| | Posted: Mon Jul 07, 2008 6:18 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
| | | |
With your dates in column A starting at A1, put this formula in B1:
=TEXT(DATEVALUE(LEFT(A1,SEARCH("
to",A1)-1)),"mmm.dd-")&RIGHT(A1,2)&".200"&IF(ISNUMBER(SEARCH(LEFT(A1,3),"JanFebMar")),"8","7")
and copy down. Note the space before the 'to' inside the first set of
quotes.
Hope this helps.
Pete
"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message
news:de629d4a-e66b-4659-bbb6-c9513fe812f7@z72g2000hsb.googlegroups.com...
| Quote: | Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help. D |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Fri Jul 11, 2008 5:19 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
| | | |
I just noticed that I did not account for the year switch-over. I am making
the assumption that the list always runs from April of last year to March of
the current year (that way, the year will always update correctly from
year-to-year). Here is the corrected code...
Sub ConvertDateFormats()
Dim YearValue As Long
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
YearValue = Year(Now) - 1
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue - _
(InStr("JanFebMar", Left(Parts(0), 3)) > 0)
End If
Next
End Sub
Rick
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:eSIjbiG4IHA.1196@TK2MSFTNGP05.phx.gbl...
| Quote: | This macro should do what you want (see note at after the code).
Right-click the tab for the worksheet with your dates on it and click
"View Code" from the popup menu that appears. When you do this, you will
be taken into the VBA editor and the code window for that worksheet will
be automatically displayed. Copy/Paste the following code into that code
window...
Sub ConvertDateFormats()
Const YearValue As Long = 2007
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue
End If
Next
End Sub
Note: Change the values assigned in the Const statements at the beginning
of the code to the year for your dates and the range of cells containing
your dates. Now, back on your worksheet, press Alt+F8, select
ConvertDateFormats from the lists and click the Run button. Your dates
should all change to the new values you asked for (this is a permanent
change, not simply a cell format).
Rick
"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message
news:de629d4a-e66b-4659-bbb6-c9513fe812f7@z72g2000hsb.googlegroups.com...
Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help. D
|
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Fri Jul 11, 2008 5:20 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
See the corrected code I just posted under my original code posting.
Rick
"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message
news:9c258d9d-447c-4666-b808-2f48ebc673ef@27g2000hsf.googlegroups.com...
| Quote: | Pete and Rick, thanks, thanks, thanks! I'll give both approaches a
try. I'm not the creator of the sheet and that's what they have and
that's what I must work with. As this workbook shows info that they
present to the higher ups, obviously must be what the brass wants.
But if I can manipulate the data more easily for charting purposes
then that will make this ideal. I don't know which one will work more
easily, though; I'll test them both out to see what will handle this
best. But great to learn 2 neat ways to do this.
Thanks. Will report back.
Cheers! D |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Fri Jul 11, 2008 5:37 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
| | | |
On the off-chance that making the change permanent could be a problem for
you, I have modified the macro to be a toggle; each time you execute the
macro, it will change the format of the values back and forth between the
"April 1 to 30" format and the "Apr.01-30.2007" format. Try it out, I think
you will like it...
Sub ConvertDateFormats()
Dim YearValue As Long
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
YearValue = Year(Now) - 1
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
If InStr(C.Value, ".") = 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue - _
(InStr("JanFebMar", Left(Parts(0), 3)) > 0)
Else
Parts = Split(Left(C.Value, 9), "-")
Parts(0) = Format(CDate(Replace(Parts(0), ".", " ") & ", 2000"),
"mmmm d \t\o")
C.Value = Join(Parts, " ")
End If
End If
Next
End Sub
Rick
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:uyhkYs44IHA.3828@TK2MSFTNGP02.phx.gbl...
| Quote: | I just noticed that I did not account for the year switch-over. I am making
the assumption that the list always runs from April of last year to March
of the current year (that way, the year will always update correctly from
year-to-year). Here is the corrected code...
Sub ConvertDateFormats()
Dim YearValue As Long
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
YearValue = Year(Now) - 1
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue - _
(InStr("JanFebMar", Left(Parts(0), 3)) > 0)
End If
Next
End Sub
Rick
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:eSIjbiG4IHA.1196@TK2MSFTNGP05.phx.gbl...
This macro should do what you want (see note at after the code).
Right-click the tab for the worksheet with your dates on it and click
"View Code" from the popup menu that appears. When you do this, you will
be taken into the VBA editor and the code window for that worksheet will
be automatically displayed. Copy/Paste the following code into that code
window...
Sub ConvertDateFormats()
Const YearValue As Long = 2007
Const RangeOfDates As String = "A1:A15"
Dim C As Range
Dim Parts() As String
For Each C In Range(RangeOfDates)
If Len(C.Value) > 0 Then
Parts = Split(C.Value, " to ")
Parts(0) = Format(Parts(0) & ", " & YearValue, "mmm.dd")
C.Value = Parts(0) & "-" & Parts(1) & "." & YearValue
End If
Next
End Sub
Note: Change the values assigned in the Const statements at the beginning
of the code to the year for your dates and the range of cells containing
your dates. Now, back on your worksheet, press Alt+F8, select
ConvertDateFormats from the lists and click the Run button. Your dates
should all change to the new values you asked for (this is a permanent
change, not simply a cell format).
Rick
"MitchellWMA" <mitchellwma1@yahoo.com> wrote in message
news:de629d4a-e66b-4659-bbb6-c9513fe812f7@z72g2000hsb.googlegroups.com...
Don't know if this will be the right group for this.
I have some spreadsheets that I must chart but the date formats are
not the best.
The sheet has the months like this:
April 1 to 30
May 1 to 31
June 1 to 30
July 1 to 31
August 1 to 31
September1 to 30
October 1 to 31
November1 to 30
December 1 to 31
January 1 to 31
February 1 to 28
March 1 to 31
When I will need the smallest yet most complete info so that if I
could convert the above quickly to format below, that would be best:
Apr.01-30.2007
May.01-31.2007
Jun.01-30.2007
Jul.01-31.2007
Aug.01-31.2007
Sep.01-30.2007
Oct.01-31.2007
Nov.01-30.2007
Dec.01-31.2007
Jan.01-31.2008
Feb.01-28.2008
Mar.01-31.2008
I haven't a clue on how to do this other than manually, which is not
desirable as too many to change.
Thanks for any help. D
|
|
| |
| | | MitchellWMA | |
| | Posted: Fri Jul 11, 2008 6:09 pm Post subject: Re: Change date format for existing sheet? (XL2K+XL2003) | |
Pete and Rick, thanks, thanks, thanks! I'll give both approaches a
try. I'm not the creator of the sheet and that's what they have and
that's what I must work with. As this workbook shows info that they
present to the higher ups, obviously must be what the brass wants.
But if I can manipulate the data more easily for charting purposes
then that will make this ideal. I don't know which one will work more
easily, though; I'll test them both out to see what will handle this
best. But great to learn 2 neat ways to do this.
Thanks. Will report back.
Cheers! D |
| |
|
|
