Import dates from text with milliseconds

[kristoh]

I'm trying to open a text file (comma separated) containing a column of dates (in the format 2008-06-29 14:48:15:973). As far as I understand, Excel needs milliseconds in the format 2008-06-29 14:48:15.973 (period as delimiter before milliseconds). When I manually change the colon to period in the cell, excel manages to recognize it as a date. However, as I have 21000 entries, I would like to do this automatically. So I create a new column, enter the formula: =replace(A4, 20, 1,".") and I there see the correct output with the '.' in stead of ':'. But Excel still doesnt recognize this as a date (so I can apply other formatting or do computation on it). Can anybody explain to me what is going on, and/or how I can automatically get Excel to recognize these values as dates? I guess it might be possible to manipulate the text file outside excel, but i'd rather do it in excel as I will be doing with several files later.
<table x:str="" style="border-collapse: collapse; width: 115pt;" border="0" cellpadding="0" cellspacing="0" width="153"><col style="width: 115pt;" width="153"><col> <tbody></tbody><tbody><tr style="height: 12.75pt;" height="17"></tr></tbody></table><table x:str="" style="border-collapse: collapse; width: 115pt;" border="0" cellpadding="0" cellspacing="0" width="153"><tbody><tr style="height: 12.75pt;" height="17"></tr></tbody></table>

 

[Lewiy]

Your formula outputs a text string. Use a mathematical operator to convert this to a number again. Either double minus, plus zero or times one:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=--REPLACE(A4, 20, 1,".")
=REPLACE(A4, 20, 1,".")+0
=REPLACE(A4, 20, 1,".")*1

 

[kristoh]

Great, thank you very much!