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

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/