Save as CSV without time formats AM/PM.

Originally Posted by Don_A

I am trying to save a file as a CSV format and have some columns with a time in them. I have tried formatting them to Custom-> hh:mm:ss which displays correctly. When I save the file they still show with AM/PM???

I have not problem saving hh:mm:ss as it appears. I think you are misinterpreting what your see.

First, AFAIK, when we save as CSV, Excel writes values to the (text) file as they appear (formatted) in the cell. So if the cell formula is =NOW(), which has today's date and current time, but it is formatted as hh:mm:ss, Excel will write only the time to CSV file in the form hh:mm:ss.

You can verify that by opening the CSV file in Notepad or the equivalent.

Second, however, if you subsequently open the CSV file directly in Excel, Excel interprets the contents as if we had typed them manually into cells formatted as General. In the case of text in the form hh:mm:ss in the CSV file, it is interpreted as hours, minutes and seconds, and its value (time serial number) is entered into the cell. Moreover, the cell is formatted as Custom hh:mm:ss.

However, as with all date and time values, if you look at the cell value in the Formula Bar, not the cell, it appears as 12-hour h:mm:ss followed by AM or PM. We have no control over the Formula Bar formatting rules. It is what it is.

If you selected the option "Edit directly in cell", which is the default, you will also see that Formula Bar format in the cell while you are editing. But once you press Enter or otherwise exit editing mode, the cell value will be displayed according to the cell format.

Does that explain what you are seeing?

Originally Posted by Don_A

The other thing that is weird about this is when I use {=MAX(LEN(A1:A20))} it says the length = 20 instead of 11 or 12 depending on time. What do I do to fix this?

If A1:A20 contain time (or date and time), string functions see the decimal representation, not the formatted form. For example, if A1 contains the time 19:39:23, =MID(A1,1,99) returns the string "0.819016203703704", which is the decimal representation of that time.

Likewise, LEN(A1) returns the length of the decimal representation; that is, the length of the string that MID(A1,1,99) returns.

Does that explain what you are seeing?

If you want the length of the formatted value, use TEXT(A1,"format"), where "format" is the same as the cell format. For example, LEN(TEXT(A1,"hh:mm:ss")).

Originally Posted by Don_A

This data was imported from a tab delimited text file with the correct time values as strings - none with AM/PM either just simple text like '20:32:01'. Excel in it's un-infinite wisdom has converted the underlying data by itself because it thinks it is smart, but it is not. How do I stop this behavior and or fix it?

I cannot duplicate the behavior you see, as I understand it, using Excel 2010.

When I import data in the form hh:mm:ss in a tab-delimited file (click on Data, Get External Data, From Text, and select Tab delimiter), the data appears in the form h:mm:ss in the cells, and the cell format is Custom h:mm:ss.

As I mentioned before, the cell value does appear in the form h:mm:ss AM/PM in the Formula Bar. And as I said before, there is nothing we can do to control that.

Nevertheless, that does not impact the appearance of the value in the cell, except when we edit the cell and the option "Allow editing directly in cells" is selected.

As for a "fix", after importing, simply change the cell format to Custom hh:mm:ss.

You can probably automate that by setting up an event macro (Worksheet_Change?). Perhaps someone else can help you with that, if that's the direction you want to go.