Interpretation of date field in csv file by excel

[huggyboy]

This seems to be a slightly new twist on a recurring issue.
I am writing a csv file using a vb.net application i have written and one of the fields is the date using the statement Format(Now, "dd/MMM/yyyy") and opening the file in notepad i can see that it is correct eg 20/Aug/2009.
However opening in excel 2007 changes the contents of the cell to 20/08/2009 (interprets date correctly) and formats to 20-Aug-09.

Excel seems to be taking these decisions on its own and i wondered if there is any way of telling it to format the date with 4 digits (for instance) as it opens the file.

There doesnt seem to be a 'default date format' setting in excel that i can find.
The regional settings on my pc are short date 20/08/2009 long date 20 August 2009 (i am in the UK)

Anybody got any thoughts on this?

 

[huggyboy]

On further investigation - csv file of
20/08/2009,dd/mm/yyyy
20/08/09,dd/mm/yy
20-08-2009,dd-mm-yyyy
20-08-09,dd-mm-yy
20/Aug/2009,dd/mmm/yyyy
20-Aug-2009,dd-mmm-yyyy
20 August 2009,dd mmmm yyyy

is displayed in excel as
20/08/2009 dd/mm/yyyy
20/08/2009 dd/mm/yy
20/08/2009 dd-mm-yyyy
20/08/2009 dd-mm-yy
20-Aug-09 dd/mmm/yyyy
20-Aug-09 dd-mmm-yyyy
20-Aug-09 dd mmmm yyyy

a new interpretation of Henry Fords - 'any colour you like as long as its black'!! - microsoft seems to give you 2 options - thats progress for you

 

[combo]

When exporting/importing to csv, excel does not copy formatting for numbers and dates. Instead uses regional settings. For dates it chooses between long and short date formats. BTW, the "c" in "csv" is the regional list separator, usually comma, but not everywhere.

combo

 

[huggyboy]

The interpretation of the date by excel is correct however my point is the formatting it automatically applies to the cell - seemingly dd/mm/yyyy if the date is numeric or dd-mmm-yy if it contains letters (ie month)

It isnt a major problem - i can reformat later - i just wondered if anyone had a solution or is it just what excel does

 

[SkipVought]

Here's some of what Excel does with respect to dates...

faq68-5827

Be VERY careful!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[huggyboy]

Yes I discovered that in my investigations before I posted.
Very useful - thanks - doesnt address this particular issue though.

 

[SkipVought]

I would not OPEN the .csv.

Rather I would IMPORT. You would have much better control of the environment!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[huggyboy]

Indeed - looks like the only dependable way around this on.
Thanks for everybodies comments

As a final parting shot csv file ...
2009-08-20,yyyy-mm-dd
2009/08/20,yyyy/mm/dd
2009-Aug-20,yyyy-mmm-dd
2009/Aug/20,yyyy/mmm/dd

opened in excel ...
20/08/2009 yyyy-mm-dd
20/08/2009 yyyy/mm/dd
2009-Aug-20 yyyy-mmm-dd
2009/Aug/20 yyyy/mmm/dd
with the interesting final wrinkle that excel doesnt seem to 'recognise' the last 2 (yyyy-mmm-dd yyyy/mmm/dd) as dates and applies general format to it

 

[SkipVought]

excel doesnt seem to 'recognise' the last 2 (yyyy-mmm-dd yyyy/mmm/dd) as dates and applies general format to it

NOT SO!!!

If you would have IMPORTED, and specified YMD in the column parse, your result would be...
[tt]
8/20/2009
8/20/2009
20-Aug-09
20-Aug-09
[/tt]
ALL REAL DATES, dispite the difference in default formatting.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Alternatively, since you ignore the IMPORT suggestion,

1. select the ENTIRE COLUMN where your imported dates & TEXT DATE STRINGS reside.

2. Data > Text to columns > NEXT

3. select NO DELIMITERS if you had the DELIMITER option.

4. NEXT

5. select DATE - YMD > FINISH

VOLA y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I am writing a csv file using a vb.net application i have written and one of the fields is the date using the statement Format(Now, "dd/MMM/yyyy") and opening the file in notepad i can see that it is correct eg 20/Aug/2009.

As a matter of practice, I would strongly recommend to ANY programmer, that exported date strings be done in yyyy/mm/dd format. It is the ONLY UNAMBIGUOUS format to use.

When you IMPORT date stings in Excel, they WILL, WITHOUT DOUBT, be converted to the correct date of intention, After which they can then be FORMATTED for DISPLAY purposes any way you wish. Could even be formatted dd ddd/yyyy-mmmm 20 Thu/2009-August, if, for some unknown reason, it were a requirement.

The REASON for doing an IMPORT, rather than an OPEN, which I assume is part of a recurring process, is that you can do all this parsing ONE TIME and format the column ONE TIME.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MasterRacker]

I agree with Skip twice.

Excel is not really a good data handling tool to begin with since it makes a lot of assumptions about both format and data type. Anything you can do to be deterministic and take the choice away from Excel is a good thing.

On top of Excel's issues, date handling in general is always a pain in the posterior.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[huggyboy]

Excellent - thanks for your input