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?
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?
ASKER
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
What about choosing Custom format and entering type: m/dd/yyyy
ASKER
Nope - did not work
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.
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.
ASKER
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.
The only thing I can guess is that it is trying to "guess" the format based on adjacent columns.
Try going to Excel Options, then Advanced, and on the right side, from near top uncheck the "Extend list formats and formulas" checkbox
ASKER
That didn't work either.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
I am not sure if you tried it on other workbooks to see if it was that specific workbook or not...
ASKER
I resolved it by saving as text then importing to excel
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.