Dates American English?

[donh]

Hi, Using Excel 2003 in England. Have an issue where Excel is opening
an Export from a custom programme and although dates are showing
correct format in the originating programme Excel 2003 is misreading
them. Any date starting with 10 and below is switching months to days
ie 10/5/10 to 5/10/10 and any dates 11 and above that Excel cannot
resolve as a date (I guess in the American format) is being formatted
as text.

Any ideas whats going on

Many thanks in anticipation

DonH

 

[Dave Peterson]

If you're opening a .CSV file through the File|Open menu, then excel will treat
each field the way it wants.

And if it sees something that looks like a date, it'll be treated as a date.
Excel also uses the user's windows regional setting short date format to
determine if ##/##/## is a valid date.

12/13/14
is a valid date in mdy order (and others), but not valid in ymd order.

You have a few choices.

You could change your windows regional short date setting (under Windows control
panel).

You could rename the .CSV file to .Txt. Then when excel opens the file, you'll
see a wizard asking how each field should be treated. You'll be able to choose
date (in the correct order for the input mdy or dmy or whatever).

Or you could use Data|Import external data and see that same wizard when you
import the .csv file.

Whatever you decide to use, I'd double check the input dates against the dates
as they're imported into excel.

I'd use an unambiguous date format: mmmm dd, yyyy

Then compare that to the input file to make sure that they represent the same date.

Currently, your data may look like dates and even be dates, but it may not
represent the original dates correctly.

 

[donh]

If you're opening a .CSV file through the File|Open menu, then excel willtreat
each field the way it wants.

And if it sees something that looks like a date, it'll be treated as a date.
Excel also uses the user's windows regional setting short date format to
determine if ##/##/## is a valid date.

12/13/14
is a valid date in mdy order (and others), but not valid in ymd order.

You have a few choices.

You could change your windows regional short date setting (under Windows control
panel).

You could rename the .CSV file to .Txt.  Then when excel opens the file, you'll
see a wizard asking how each field should be treated.  You'll be able to choose
date (in the correct order for the input mdy or dmy or whatever).

Or you could use Data|Import external data and see that same wizard when you
import the .csv file.

Whatever you decide to use, I'd double check the input dates against the dates
as they're imported into excel.

I'd use an unambiguous date format:  mmmm dd, yyyy

Then compare that to the input file to make sure that they represent the same date.

Currently, your data may look like dates and even be dates, but it may not
represent the original dates correctly.

Thank you for your reply