Date format on import

[ptoye]

How can I force the text input mechanism to use European
format rather than American format on a date? It's in a
single cell only, not an entire column. As far as I can
see, if the date is a valid mm/dd/yy date it's imported in
American format, otherwise if it's valid in dd/mm/yy
format it seems to import in European format (but I've not
tested this much).

Not much use around this time of the year! About time that
MS learned about locales IMHO.

 

[Stephen Bullen]

Hi Ptoye,

How can I force the text input mechanism to use European
format rather than American format on a date? It's in a
single cell only, not an entire column. As far as I can
see, if the date is a valid mm/dd/yy date it's imported in
American format, otherwise if it's valid in dd/mm/yy
format it seems to import in European format (but I've not
tested this much).

If you use the Text Import Wizard, it's one of the options when
specifying the data formats. The same is available using the .OpenText
method in VBA. It's easiest to record yourself doing it to get it
right.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk