Solved

MS Excel 2010 - Format Column As Date

Posted on 2013-06-19
12
428 Views
Last Modified: 2013-06-24
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?
0
Comment
Question by:CipherIS
  • 6
  • 6
12 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39259695
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
 
LVL 1

Author Comment

by:CipherIS
ID: 39259794
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39259808
What about choosing Custom format and entering type:  m/dd/yyyy
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:CipherIS
ID: 39259822
Nope - did not work
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39259871
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
 
LVL 1

Author Comment

by:CipherIS
ID: 39259949
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39260005
The only thing I can guess is that it is trying to "guess" the format based on adjacent columns.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39260034
Try going to Excel Options, then Advanced, and on the right side, from near top uncheck the "Extend list formats and formulas" checkbox
0
 
LVL 1

Author Comment

by:CipherIS
ID: 39260279
That didn't work either.
0
 
LVL 1

Accepted Solution

by:
CipherIS earned 0 total points
ID: 39260283
I resolved it.  What I did was I saved the spreadsheet as a text file.  Then I imported the text file.  Go figure.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39260292
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
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 39270694
I resolved it by saving as text then importing to excel
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question