Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a date in text format xx.xx.20xx to a recognised date format
I often have to deal with a column of text in the format of xx.xx.19xx. However this is not a recognised date format. Is there a macro or command that will allow me to select all of thes cells and convert them into a recognised date format, eg xx/xx/19xx o xx-jan-19xx? At present i can split them out to columns and then concatenate th data into a date format but this only works the cell in Row 1,not fo the whole selection. Any suggestions? Cheer ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a date in text format xx.xx.20xx to a recognised date format
I think the easiest way would be to select the column and do a global
replace of "." for "/". You could record a macro as you do it ... should look something like this: Columns("B:B").Select Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False A slightly modified version: Columns("B:B").Replace _ What:=".", _ Replacement:="/", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False In my testing the text was turned into dates with a date format "dd/mm/yyyy" (which is what I expected). Be careful if you opt for a VBA solution as dates can become "muddled" by the USA-centricity of VBA Regards Trevor "concatenator" wrote in message ... I often have to deal with a column of text in the format of xx.xx.19xx. However this is not a recognised date format. Is there a macro or command that will allow me to select all of these cells and convert them into a recognised date format, eg xx/xx/19xx or xx-jan-19xx? At present i can split them out to columns and then concatenate the data into a date format but this only works the cell in Row 1,not for the whole selection. Any suggestions? Cheers ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
how do i change text format date to date (i.e., mm/yy to mm/dd/yyy | Excel Discussion (Misc queries) | |||
CHANGE TEXT TO DATE FORMAT | Excel Discussion (Misc queries) | |||
Change date format to text format | Excel Programming |