Excel 2007 date format

Hi All,

I am using Excel 2007 and noticed that when importing a DBF file the dates are displayed as 11/06/2009 in some rows and 20091117 in other rows.

1) This happens only in Excel 2007 only as we have imported the same DBF file on Excel 2003 and 2013.

2) It looks like there is a clear divide between dates where the day and month can be inverted (US to UK), i.e. 20091117 will be recognised as text whereas 11/06/2009 will be recognised as a date.

Is anyone able to shed some light as to why this is happening?

I looked at UK date locales and regional settings but I may have missed something.

many thanks in advance.
Ben CampbellIT User, Manager, DiplomatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Is the divide as simple as a day/month "mismatch"?

20091117 <- 17 isn't a valid month
11/06/2009 <- 11 & 6 could be both a month & a date (day of the month)

Can you export the date values in [D-Mmm-yyyy] format instead?

BFN,

fp.
0
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Sorry I don't actually export anything. I'm simply opening a DBF file in excel.

I have looked at the DBF in a pure DBF viewer and notice that the dates are in the standard UK date format dd/mm/yyyy.

Why it gets formatted to something like 20091117 I don't know.

Also the ones that appear in the 20091117 format have this error "the number in this cell is formatted as text or preceded by an apostrophe"

Does this help?
0
[ fanpages ]IT Services ConsultantCommented:
It looks like this "hotfix" addresses your issue:

[ http://support.microsoft.com/kb/969952 ]

Issues that this hotfix package fixes
•When you export a pivot table from Office Web Components (OWC), Excel 2007 may crash.

When you import .dbf files into Excel 2007, Excel 2007 incorrectly imports the dates if the regional setting is set to a locale that uses the "dd/mm/yyyy" date format.

•If you paste a chart as a linked Excel chart object, the chart cannot be updated automatically.

•If the conditional formatting is applied to the column, it may take a long time to show the filter menu.

The "hotfix" can be downloaded from:
[ http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=969952&kbln=en-us ]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Installing service pack for Office 2007 resolved the issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.