CSV Date Formats

[sjp1966]

Hi

I wonder if someone could help with a date format issue in csvs, I have done a google search and I just dont understand some of the answers that Ive found

Product: Excel 2010
O/S: Windows 7 64bit

I have a guy at work that has exported a csv file, in it are dates, they need to export this file into another application and the date need to have the format as yyyy-mm-dd, we are seeing them as dd/mm/yyyy.

Ive tried chnaging input language, ive tried doin the import as data > comma delimited then changing the column heading to YMD, but to no avail. Is there an easy way to change the date format of CSV document?



thanks

Steve

[OTPM]

Hi
Firstly highlight all the cells you want to change the date format.
Then right click anywhere in the highlighted area and choose format cells.
Then choose Custom.
Then enter the following format: yyyy-mm-dd.
This will reformat all your dates.
Hope this helps.
Good luck.
Tony

[BobBridges]

Steve, one thing you didn't specify: Where are you seeing these dates as dd/mm/yyyy? a) In the workbook before you export to a CSV? b) In the CSV after you've created it from Excel? Or c) in the application on the other side, after it reads the CSV?

OTPM gave you the solution for a), which is the reasonable assumption if we have to assume at all. I think it's right for b), too, though I haven't tried it to be sure. But if your problem is different, you gotta say so, 'cause it might make a difference.

[sjp1966]

Hi Bob

Sorry. This is when we view the CSV in Excel. I am actually thinking that this maybe an issue with excel.

I am being given a CSV file.
I open it in excel and the dates are dd/mm/yyyy
I reformat dates to yyyy-mm-dd
when I save I get the contains formatting error that happens on every csv in the world I think.
When I relook in excel the date revert to dd/mm/yyyy
however if I look at the CSV in notepad they are the yyyy-mm-dd format
so I am wondering if excel is changing this information.

[BobBridges]

Yeah, that's what I find, too. Excel is writing the dates to the CSV in whatever format you specify, but when it reads a CSV back in—remember, a CSV is plain text, so there are no formatting codes to guide Excel—it just has to make up its own mind. Perhaps there are some formats it simply can't be sure about. I've done a few tests:

yyyy-mm-dd: Upon reading the CSV back into Excel, it's still in yyyy-mm-dd. This result sounds different from yours but it isn't really, because yyyy-mm-dd is the default date format on my machine; I set it in the Control Panel, Regional Settings; so Excel is just following that default.

dddd, mmmm d, yyyy: Excel writes this out to the CSV as a text string ("Tuesday, September 17, 2013"), so that's the way it reads it back in, too; doesn't even know it's a date.

dd-mmm: This comes back as dd-mmm, just as it should. But:

m-d: This comes back as dd-mmm, too. Go figure.

m-dd-yy and mm-dd-yy: These are written to the CSV without quote marks, ie not as character strings. And when they're read back into Excel, they're displayed as they went out; but they're now left justified, and their format reads General rather than a date format. Weird.

dd-mmm-yy: This displays correctly when read back into Excel, ie "17-Sep-13". But:

mmmm dd, yyyy: This was written out to the CSV as "September 17, 2013", with quotes, as a character string. But when it was read back into Excel, it was interpreted correctly as a date—and displayed the same as the above, dd-mmm-yy or "17-Sep-13". Again, weird.

mmm-yy and mmmm-yy: These went to the CSV without quotes (as Sep-13 and September-13), so when Excel read them back in it accepted them as dates. But it didn't know that 13 was meant as a year, so it assigned the format dd-mmm and displays them both as "13-Sep".

There are other departures, some of them equally puzzling, but the upshot is this: Your dates are getting out to the CSV as you intend, but when you read the CSV back into Excel, Excel is making up its own mind about how to display them. So all you have to worry about is whether that other application, the destination app, is reading your dates as you intend.

[sjp1966]

I think work must have locked down our regional settings, when i go into my Windows & at home I get the option for long and short date formats but work machines do not show that, I wonder if that is where the issue may lay.

[BobBridges]

I'm a contractor and have worked for many different clients over the past couple decades; I find that many workplaces do that sort of thing. There are reasons to lock down certain functions, but some folks seem to have a reflex to lock down everything except a few items, rather than the more sensible path of locking down only the options they know to be a risk. For example, I use the Dvorak keyboard layout rather than QWERTY. Some places have that locked down, too, so I have to spend the first few days of any gig finding the right person who can give me special permission to do perfectly risk-free things.

However, it isn't clear to me that this should be causing you a problem in this case. No matter what regional settings you pick, Excel will not read every date format in a CSV and preserve it inside Excel. That's the nature of a CSV; it's just a plain text file, and formatting is not retained.

The real question, as I see it, is this: When you take that CSV and import it into the other application (and what is that other application, by the way?), how does it see it? If it knows it's a date, and can correctly identify which parts are the year, month and day, then you should be able to go on from there. No?

[sjp1966]

The CSV is going to a 3rd party so i am unsure what they are putting it into. I've viewed the CSV in notepad and it displays ok, so the raw csv is ok.