Solved

MS Excel 2010 - Format Column As Date

Posted on 2013-06-19
12
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

691 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