Change a date in text format xx.xx.20xx to a recognised date format

  • Thread starter Thread starter concatenator
  • Start date Start date
C

concatenator

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
 
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
 
Back
Top