Date Format
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! |
Date Format
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! |
Date Format
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! |
Date Format
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! |
Date Format
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! |
All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com