Date field

[step10]

Hello,



I have a .csv file that automatically opens up in Microsoft Excel 2010 and one of the fields is a date field that displays as Mar-13 (example);, but when you click in the field, it shows "March 13, 2013". How do I get this to display as is and not be automatically converted?

I've tried setting it as custom and put the date format in and I've tried setting it to a text field, but nothing seems to work.

Is there something in Excel, that automatically converts the date field to a specific format, and if so, how do I turn this off?

Thanks.

[macropod]

If you want 'Mar-13' to remain 'Mar-13', you would probably need to use either:
• Excel's Data>Get External Data>From Text function and set the comma delimiter then format the affected column as text; or
• a macro to convert the date back to a string after opening in Excel.
Note that you'll need to do this every time you open the csv file.

The alternative is to use a macro to read the csv file into Excel one line at a time and output each affected field in text format. This would be a much slower process, though, and would still need to be done every time you open the csv file.

[BobBridges]

What you have to understand, step10, is that a CSV file is a sort of extract of a single worksheet that contains just the text of each cell's value. No formulae, just the results. No formatting, which means no fonts, colors, borders or special output formats, including dates.

Therefore when you read in a CSV, Excel can see only the value, not the way you want it expressed. If you want to save a date format from one session to the next, you have to save the worksheet in one of the formats (using Save As) that isn't text only. That's most of them; the default is .xls[x], and it'll save all that stuff for you. Really CSV is intended only to help you transfer data between Excel and other applications.

[macropod]

Originally Posted by BobBridges

Therefore when you read in a CSV, Excel can see only the value, not the way you want it expressed.

Bob,

I think you've misunderstood the issue, which is that Excel is converting the text string 'Mar-13' in the CSV file to the date 'March 13, 2013'. If Excel truly returned only the content of the CSV as you suggest, instead of 'interpreting' it, that wouldn't happen.

[BobBridges]

You may be right, Paul; I'm certainly not understanding something.

Here's what I think I'm hearing: In the CSV is some text that reads "...<date>...", with commas around it. When Excel reads the CSV, step10 reports that the date is displayed as "Mar-13" in the cell, but when he clicks on it it displays "March 13, 2013" in the formula bar. (Right, step10?) He reports this as a problem, believing that Excel is "changing" something. Me, I don't think that's what's happening; I think all that's happening is that Excel is choosing the default format for displaying dates. And I suspect that step10 has already tried changing the format of the date to suit him, then saved the worksheet again—still as a CSV—and is frustrated that when he opens the CSV again later, the date is no longer in the format he selected, but back to the original one.

You suggested some ways he could ensure that the format is set correctly (again) every time he opens the CSV. I went on to explain why a CSV can never actually retain the format from one session to the next.

So the issue (I think) is that Excel is behaving as it's designed, but step10 simply didn't understand what a CSV is supposed to be. But you say the issue is ... I'm not sure I follow it. Am I missing something, or did I just phrase it misleadingly, the first time?