Import of a txt file (missing information)

[franzpeter]

Hi,

if I try to import a txt file with lines like the following via Excel import file:

xxxxxxxx Long 11/16/2017 8:57:02.0 11/16/2017 09:14.0 6340 6340 1 1 1 -4.1 -4.1 25 -15 25 -15 62.50% 37.50% 0.00% 4.1 6341.25 6339.25

for the first time data 8:57:02.0 the hour 8 information is missing in the appropriate column, for the second time data sometimes also the hour information is missing and it looks like:


xxxxxxxx Long 11/16/2017 57:02.002 11/16/2017 14.001 6340 6340 1 1 1 -4.1 -4.1 25 -15 25 -15 62.50% 37.50% 0.00% 4.1 6341.25 6339.25

Any recommendations/suggestions ?

 

[Special-K99]

When you say you "import" the file, I presume you simply mean opening it in Excel and changing the filetype to txt so Excel picks up text files too ?

Your time is NOT an Excel time

The value is "8:57:02.0"

That's hour minutes seconds and some other part of a second

Excel expects time to be hh:mm:ss so "8:57:02" would be fine but not "8:57:02.0"

 

[Special-K99]

OK, looking at this closer, the hour is not actually missing from the value, it is just not being displayed correctly.
If you click on the cell it displays 8:57:02 in the formula bar.

Format the cell as Custom and select hh:mm:ss.000 and it will display the time correctly in the cell.

 

[franzpeter]

Thank you very much for the help !