Importing timestamps with milliseconds

[farnol]

I try to import an instrument log with timestamps in format YYYY-MM-DD hh:mm:ss.sss into Excel. Whenever the first digit after the decimal separator is a zero, Excel doesn't recognise the value as valid date time, but import it as text.

E.g. "2014-11-27 03:38:34.853" is imported as time, but "2014-11-27 03:38:35.079" is imported as text.

I have also tried to import everything as text, and using the TIMEVALUE() function, but runs into the same kind of problem. The first value in the example above evaluates fine, the second gives a #VALUE! error.

I guess I can use some string manipulation to finally get the information I need for my purpose, but does anyone have a clue what is going on here?

 

[Andrew Poulsom]

Welcome to MrExcel.

I can't reproduce the behaviour you are seeing in Excel 2010. Are the timestamps in a txt file? Can you post some sample data from the file that demonstrates the problem.

 

[farnol]

I'm using Excel 2013, if that matter. I'm importing from a tab delimited text file. Here is a small sample with only a few columns after the timestamp:

Time (UTC) dTime Az Actual El Actual
YYYY-MM-DD hh:mm:ss.sss (sec) (deg) (deg)
2014-11-24 07:54:36.225 0.000 262.166 2.315
2014-11-24 07:54:36.325 0.100 262.166 2.315
2014-11-24 07:54:36.425 0.200 262.166 2.315
2014-11-24 07:54:36.525 0.300 262.166 2.315
2014-11-24 07:54:36.625 0.401 262.166 2.315
2014-11-24 07:54:36.725 0.501 262.166 2.315
2014-11-24 07:54:36.826 0.601 262.166 2.315
2014-11-24 07:54:36.926 0.701 262.166 2.315
2014-11-24 07:54:37.026 0.801 262.166 2.315
2014-11-24 07:54:37.126 0.901 262.166 2.315
2014-11-24 07:54:37.226 1.001 262.166 2.315
2014-11-24 07:54:37.326 1.102 262.166 2.315
2014-11-24 07:54:37.426 1.202 262.166 2.315
2014-11-24 07:54:37.527 1.302 262.166 2.315
2014-11-24 07:54:37.627 1.402 262.166 2.315
2014-11-24 07:54:37.727 1.502 262.166 2.315
2014-11-24 07:54:37.837 1.612 262.166 2.315
2014-11-24 07:54:37.937 1.712 262.166 2.315
2014-11-24 07:54:38.037 1.813 262.166 2.315
2014-11-24 07:54:38.138 1.913 262.166 2.315
2014-11-24 07:54:38.238 2.013 262.166 2.315
2014-11-24 07:54:38.338 2.113 262.166 2.315
2014-11-24 07:54:38.438 2.213 262.166 2.315
2014-11-24 07:54:38.538 2.313 262.166 2.315
2014-11-24 07:54:38.638 2.413 262.166 2.315

In this sample are the timestamps "2014-11-24 07:54:38.037" and "2014-11-24 07:54:38.037" imported as text. All the others are imported as time. If I just type values into a blank unformatted work book, I'm seeing the same behaviour. The values where the first digit after "." is not a zero is interpreted as time, but not when it is a zero.

EDIT: I can see that the tabs got lost when I pasted the sample. There is only four columns, in the first one there is a space in the string.

 

[Andrew Poulsom]

That imports fine for me in Excel 2013 (with English(UK) Regional Settings).

Do you want to put a sample text file on a share like Box.com and post the URL?

 

[farnol]

I changed the regional settings to English (UK), and it imported fine here as well. I can live with that the time it takes to complete this task. My usual regional setting is Norwegian, but with Excel set to use English style decimal and thousand separators instead of system settings.

In case someone like to investigate this further, I have uploaded a compressed sample file and a screen dump of an Excel Workbook after importing those data with the above described settings.

 

[Andrew Poulsom]

From a quick test, Norwegian settings work with a comma instead of a dot before the milliseconds in the text file.