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. :oD

Mitchell,

I don't see why you need all the extra information (e.g., 1-30, 1-28) 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:
> 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. :oD

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. :oD

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...
> 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. :oD

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! :oD

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. :oD
>

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...
> 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! :oD

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...
>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. :oD
>>
>