MS Excel 2010 - Format Column As Date

I exported data to an xls file.  When I open the file in Excel I am trying to format a column.  I clicked on the column and changed it from General to Short Date.  I need the date to be correct so it will sort correctly.

However, changing column to short date does not resolve it.  Date 4/9/13 is between 4/30/13 and 5/10/13.  When I try to change 4/9/13 to 4/09/13 it displays instead 41373.469583333.

Any ideas?
LVL 2
CipherISAsked:
Who is Participating?
 
CipherISConnect With a Mentor Author Commented:
I resolved it.  What I did was I saved the spreadsheet as a text file.  Then I imported the text file.  Go figure.
0
 
NBVCCommented:
41373.469583333. is an Excel serial number representing date and time.

If you right-click and choose Format|Cells, then in the Number tab, select Data and choose your format... it should give you the date.
0
 
CipherISAuthor Commented:
I did what you suggested prior to posting.  It did not work.  I changed the date to display as 04/09/13 but its displays 4/9/13 11:16:12 AM
0
The 14th Annual Expert Award Winners

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

 
NBVCCommented:
What about choosing Custom format and entering type:  m/dd/yyyy
0
 
CipherISAuthor Commented:
Nope - did not work
0
 
NBVCCommented:
If you are not using an English version of Excel, it could be different than m/dd/yyyy depending on your language.

otherwise, I can't see why it doesn't work, if you have 41373.469583333 and can format to show 4/9/13 11:16:12 AM , then you should be able to custom format it with m/dd/yyy.
0
 
CipherISAuthor Commented:
I have the English version of Excel.  The date shows up  4/9/13 11:16:12 AM.  When I make a change then it converts to 41373.469583333.
0
 
NBVCCommented:
The only thing I can guess is that it is trying to "guess" the format based on adjacent columns.
0
 
NBVCCommented:
Try going to Excel Options, then Advanced, and on the right side, from near top uncheck the "Extend list formats and formulas" checkbox
0
 
CipherISAuthor Commented:
That didn't work either.
0
 
NBVCCommented:
Yes, strange, there must have been so stuck setting or something... but good that it is resolved.

I am not sure if you tried it on other workbooks to see if it was that specific workbook or not...
0
 
CipherISAuthor Commented:
I resolved it by saving as text then importing to excel
0
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.

All Courses

From novice to tech pro — start learning today.