I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?

Sub DateFormat()

Range("z2:z1000").Select
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Sub
--
Thank you!

Note that this relies on the local date format; if you are working with an
international audience you may have to check the PC's date format and branch
your code to restructure the date string accordingly.
HTH,
Keith

Sub convertdate_month()
sourcestring = "20100205"
'put in a recognizable date format
newString = Mid(sourcestring, 4, 2) & "-" & Right(sourcestring, 2) & "-" &
Left(sourcestring, 4)
ActualDate = CDate(newString)
ActualMonth = MonthName(Month(ActualDate))
msgbox ActualDate & chr(13) & ActualMonth
End Sub


"Doug" wrote:

I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?

Sub DateFormat()

Range("z2:z1000").Select
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Sub
--
Thank you!

A formula will do the trick. In row 2 of your new column, the formula would
be:
=TEXT(z2,"mmm")

Copy said formula down the length of your table.

"Doug" wrote:

I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?

Sub DateFormat()

Range("z2:z1000").Select
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Sub
--
Thank you!

I am using this =VLOOKUP(R3,Import!P:CA,11,FALSE) to retrieve the data from a
web import. Can I combine the two formulas somehow?
--
Thank you!


"B Lynn B" wrote:

A formula will do the trick. In row 2 of your new column, the formula would
be:
=TEXT(z2,"mmm")

Copy said formula down the length of your table.

"Doug" wrote:

I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?

Sub DateFormat()

Range("z2:z1000").Select
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Sub
--
Thank you!

I think I need to clarify. The previous date format for "20100205" works fine.
I am just needing a macro that will turn "10" to a "Nov" format. I just
thought that you may be able to modify the other for this application?
--
Thank you!


"ker_01" wrote:

Note that this relies on the local date format; if you are working with an
international audience you may have to check the PC's date format and branch
your code to restructure the date string accordingly.
HTH,
Keith

Sub convertdate_month()
sourcestring = "20100205"
'put in a recognizable date format
newString = Mid(sourcestring, 4, 2) & "-" & Right(sourcestring, 2) & "-" &
Left(sourcestring, 4)
ActualDate = CDate(newString)
ActualMonth = MonthName(Month(ActualDate))
msgbox ActualDate & chr(13) & ActualMonth
End Sub


"Doug" wrote:

I have been using the following macro to change "20100205" into a date format.
Now I am also needing to change "10" to a date format so that I can have it
show "Oct" in a given field. How can I do this?

Sub DateFormat()

Range("z2:z1000").Select
Selection.TextToColumns Destination:=Range("z2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
End Sub
--
Thank you!