Change date format for existing sheet? (XL2K+XL2003)

[MitchellWMA]

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\)]

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

 

[Pete_UK]

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]

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\)]

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\)]

See the corrected code I just posted under my original code posting.

Rick

 

[Rick Rothstein \(MVP - VB\)]

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