Excel reads time from CSV incorrectly?

[elkinm]

I am trying to read a log from a CSV file where the first row is always the time.
The time format is HH:mm:ss.fff in quotes, but Excel sees it as something entierly different.

I knew excel was messed up with default date and time formats, but how does it format a standard format like this.

For example the entry of 09:21:08.666 became 21.08.7 in mm:ss.0 format.

The entries of the CSV are in quotes to ensure they are exact, but it seems excel still cannot read it correctly.

Is there any way to force excel to read csv files exactly as text with no formatting?

Also, I can modify the log string, is there a way I can format the date in the CSV source file to make it CSV legal, and force excel to read it. Something like put a "'" before the data?

Thanks

 

[Nothinman]

Is it just a display error? If you tell Excel to format the row as time does that help?

 

[elkinm]

Good point, I tried several format options with general entries.

I entered 15:15:28.254
It became 15:28.3 and the formula cell became 3:15:28 PM. The format is mm:ss.0.
I just don't understand how it thinks that not showing data is a proper format. In most online posts like this the problem is that non data or time values are formatted as such, here I have a proper complete time values and somehow it decides to ignore some of the data?

I did change the format to hh:mm:ss.000 AM/PM and is displayed as 03:15:28.254 PM which is close enough.
If I use hh:mm:ss.000 (without AM/PM) it displayed as 15:15:28.254 which is exactly what I wanted.
However, the formula entry was still 3:15:28 PM without the ms field, which is strange as I don't know how the ms is stored.
However, if I save the CSV file I loose at least the ms data, which is important.

If I added a ' (single quote) before the time the filed would remain as is, but the CSV file needs to be generic, so any non-Escel csv viewer should be able to see it normally, and I don't think a single quote is always ignored.
I did try double quotes, even double double quotes and still cannot have excel open it exactly.
If I do a data import to plain text it does work, however, I would like to avoid doing this unless there is a right-click excel import option for csv files strait from explorer

Thanks again.

 

[Binky]

Unless I'm missing something, your format isnt telling excel to display the hour, so it is displaying it as instruted.

mm:ss.0 = minutes:seconds.fractional seconds trimmed to 1 decimal point

As instructed, it is stripping the hour and trimming the fractional second to one decimal point.

Try a format of hh:mm:ss.000 to see everything.