• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

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 Campbell
Ben Campbell
  • 2
  • 2
1 Solution
[ fanpages ]IT Services ConsultantCommented:

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?


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?
[ 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 ]
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Installing service pack for Office 2007 resolved the issue
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now