Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Excel 2010 - Format Column As Date

Posted on 2013-06-19
12
Medium Priority
?
441 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

581 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