Date changes to unknown number

Question

I've imported a simple spread sheet from Access.  It is a list of names with columns of data for each name.  One column is a date.  When I type in today's date 5/1/12 and hit enter it changes it to a number, 41030, even though I have set the format to date in the format cells dialogue box.  I have office 2010 running in compatibility mode on Windows XP

Answer 1

To  remove leading apostrophes...either of these *might* work, depending on the situation

Try this:

• Select you single-column range of cells to be impacted

• Data.Text-to-columns...Click: Finish

or this:

• Copy a blank cell

• Select the range of cells to be impacted

• Home.Paste.Paste_Special…Check: Add…Click: OK

Answer 2

Not so much...

Try this:

On a new worksheet in a new workbook

• Enter 41030 in cell A1

• Format that cell as a date

Does the result translate into 01-May-2012 (or some representation of that date)?

Answer 3

As far as I can tell the date format is engaged... at least it says date at the top of the number box.

I've found that if I put an * before the date I'm trying to enter that it works but I don't really want all my dates preceded by *.  Does that give you any clues about what to do?

Answer 4

Yes, it works fine in a new worksheet in a new workbook.

Answer 5

Can you verify that the date format is actually engaged?

41030 is the Excel date serial number for: 01-May-2012