Solved

MS Excel 2010 - Format Column As Date

Posted on 2013-06-19
12
397 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
Comment Utility
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
Comment Utility
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
Comment Utility
What about choosing Custom format and entering type:  m/dd/yyyy
0
 
LVL 1

Author Comment

by:CipherIS
Comment Utility
Nope - did not work
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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
Comment Utility
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
Comment Utility
That didn't work either.
0
 
LVL 1

Accepted Solution

by:
CipherIS earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I resolved it by saving as text then importing to excel
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
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.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 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

6 Experts available now in Live!

Get 1:1 Help Now