Solved: Problems formatting different date formats

[satyen]

hello,

Ive tried a number of ways and different codes, but nothing seems to work.

I am trying to format different date formats in a column so that they are ALL uniform i.e. dd/mm/yyyy (english, united kingdom)

I even recorded a macro and this is the code


[vba]
Columns("A:A").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "dd/mm/yyyy;@"

[/vba]

sample dates are:
12/05/08 12:23:06
05/05/2008 13:14:08
10-May-08
13/05/08
09-May-2008

Please note: some date will have times with them. The xl. sheet needs to be converted to a csv file after

Please can someone help?

Many Thanks.

[Bob Phillips]

Maybe they are not real dates.

See if this works any better

[vba]

With Columns("A:A")

.TextToColumns Destination:=.Cells(1, 1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(10, 1)), _
TrailingMinusNumbers:=True
.Copy
Range("C1").PasteSpecial Paste:=xlPasteValues
End With
Columns("C:C").NumberFormat = "dd/mm/yyyy;@"
[/vba]

[satyen]

Thanks, kinda works. But now its swapping the months and days around. At least the Year is not changing to something funny like it was before! thanks

[Bob Phillips]

Are the dates valid but just formatted US style, or does it change 10th Sep to 9th Oct for example?

[satyen]

Valid dates. After code is run it goes from 12/3/2008 to 03/12/2008. Only the ones with the times mind you. Rest is ok. Should stay as 12/03/2008

[Bob Phillips]

Maybe this

[vba]

Dim i As Long
Columns("A").Copy Range("C1")
Columns("C").TextToColumns Destination:=Range("C1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(10, 1)), _
TrailingMinusNumbers:=True
For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row
With Cells(i, "C")
.Value = CDate(Cells(i, "C").Value \ 1)
.NumberFormat = "dd/mm/yyyy"
End With
Next i
[/vba]

[satyen]

I turned all dates into an absolute number. i.e. =ABS(cell) then applied the format dd/mm/yyyy;@ to it. This seems to work for any date format. Thanks for your effort xld