Solved

MS Excel 2010 - Format Column As Date

Posted on 2013-06-19
12
408 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office.Com /myAccount 9 41
Fixing a embedded format 7 29
remove upper case characters in excel 4 21
Excel range I cannot find 8 26
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now