Bug - Excel treating dates as text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...db70a18&dg=microsoft.public.excel.crashesgpfs
 
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.
Click to expand...

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
 
Back
Top