Help in changing UDT DATE format plz

[excelslave]

I'd like to thank anyone in advance for help on this.

I have a row of UDT date time stamps that are formatted as TEXT and arranged
as YY/MM/DD. I need to get rid of the U and trailing space and have the end
result be DD/MM/YYYY keeping the time with it.

I've searched and tried many different ways including text to columns and then
tried to rejoin them in order and then format but the results never work out.

I prefer code since the column will have 20k+ rows of data.

The end result location doesn't matter, same column as original data is ultimate though.

Thanks in advance for ANY help,

Excel Workbook
	A	B	C
1	SyncTime A		SyncTime B
2	U 11/05/09 13:42:46.316549	End result >>	09/05/2011 13:42:46.316549
3	U 11/05/09 13:42:46.316549	End result >>	09/05/2011 13:43:41.316549
4	U 11/05/09 13:43:41.369082	End result >>	09/05/2011 13:43:41.369082
5	U 11/05/09 13:43:41.369085	End result >>	09/05/2011 13:43:41.369085
6	U 11/05/09 13:45:01.291638	End result >>	09/05/2011 13:45:01.291638
7	U 11/05/09 13:45:01.291638	End result >>	09/05/2011 13:45:01.291638
8
9	Column is formatted as TEXT		Column is formatted as TEXT
10	IS > YY/MM/DD		NEED > DD/MM/YYYY
11
testing

Excel 2003

Pat

 

[VoG]

Try

=DATE(2000+MID(A2,3,2),MID(A2,6,2),MID(A2,9,2))+RIGHT(A2,15)

 

[p45cal]

Sub UDTtoSomethingElse()
For Each cll In Range("A2:A7").Cells    'adjust or use the next (commented-out) line instead which acts on the selected cells
'For Each cll In Selection.Cells
    zz = Split(Application.Trim(cll.Value), " ")
    mydate = Split(zz(1), "/")
    mystr = mydate(2) & "/" & mydate(1) & "/" & "20" & mydate(0) & " " & zz(2)
    cll.Offset(, 2).Value = mystr    'or to update in situ use next commented-out line instead:
    'cll.Value = mystr
Next cll
End Sub

 

[excelslave]

Sub UDTtoSomethingElse()
For Each cll In Range("A2:A7").Cells    'adjust or use the next (commented-out) line instead which acts on the selected cells
'For Each cll In Selection.Cells
    zz = Split(Application.Trim(cll.Value), " ")
    mydate = Split(zz(1), "/")
    mystr = mydate(2) & "/" & mydate(1) & "/" & "20" & mydate(0) & " " & zz(2)
    cll.Offset(, 2).Value = mystr    'or to update in situ use next commented-out line instead:
    'cll.Value = mystr
Next cll
End Sub

Thanks p45cal and VoG, for the fast response. I ended up using p45cal's since it
better suited the task better. I DO APPRECIATE IT.

Thanks,
Pat

You guys rule..