Bug - Excel treating dates as text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cascada - 29 Aug 2006 17:04 GMT

When importing files with dates into excel I often get some of the dates
being treated as text and others being recognized as dates.

The data is being imported into a new blank worksheet without any formatting
attached so there is no reason for Excel to treat these dates in a random
fashion.

For example I will import a file from Sales Force as an example with a
column filled with dates and some will appear in Excel right justified and
others will appear left justified which means they are being treated as text.

This is a bug which has been know for quite some time. I know that there are
many many people out there who would appreciate it if this could be fixed.

Reply to this Message

Jan Karel Pieterse - 30 Aug 2006 06:23 GMT

Hi Cascada,

> When importing files with dates into excel I often get some of the dates
> being treated as text and others being recognized as dates.
>
> The data is being imported into a new blank worksheet without any formatting
> attached so there is no reason for Excel to treat these dates in a random
> fashion.

I doubt whether this is a true bug. What happens depends on two things:

- how you start the import (form the user interface or from VBA)
- what format the dates are in (US format e.g. mm/dd/yyyy or other format, e.g.
mm-dd-yyyy)

If started from the UI, Excel will use the controlpanel date format, unless you
specify otherwise in the text import wizard.

If started from VBA, Excel uses the US date format, unless set differently in
the arguments for the text import wizard.

So if your dates are in dd-mm-yyyy format and Excel tries to interpret them as
being mm/dd/yyyy (USA format) all dates that look like a US date are converted
to a (wrong!) date and all dates that do not comply are made text:

31-12-2006 becomes text
01-06-20006 becomes January 6 2006 (!)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Reply to this Message