asked on

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.

[ fanpages ]

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.

Ben Campbell

ASKER

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?

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ben Campbell

ASKER

Installing service pack for Office 2007 resolved the issue