Solved Cannot get date to sort correctly. See example.

April 16, 2013 at 09:43:00
Specs: Windows XP
Does not seem to matter which I choose oldest to newest or newest to oldest. I have tried changing the date format. thanks Maggie

5/10/2012
5/21/2012
8/20/2012
12/2/2012
2/2/2013
2/27/2013
2/8/2012
1/22/2013
1/26/2012
11/9/2011
3/13/2012
3/18/2013


See More: Cannot get date to sort correctly. See example.

Report •

✔ Best Answer
April 16, 2013 at 11:45:53
The only thing I can think of is your dates are getting stored as text. I found this link that explains what I'm talking about.

http://office.microsoft.com/en-us/e...

Let me know if this helps.



#1
April 16, 2013 at 10:09:45
Are you using Microsoft Excel? Try highlighting all of your dates and changing the cell format to Date by pressing Ctrl+1 in your open workbook and selecting the date option on the Number tab.

After doing that, try sorting again.


Report •

#2
April 16, 2013 at 10:22:47
Yes, sorry using Exel. I have choosen the date option, resorted, still does not sort correctly. Did a mock with dates only and had no issue sorting. Something about the worksheet I created, which only has about six column headings. Tried custom sort, indicating I have headers. Ummmm....Anyone have ideas what to try next?

Report •

#3
April 16, 2013 at 10:59:37
When you click on the cell, what does Excel show as the contents of the cell in the formula bar just above the worksheet?

Report •

Related Solutions

#4
April 16, 2013 at 11:07:04
The formula bar shows the date 2/11/12 in this format. I have tried putting it in manually, I know crazy, right! i.e. February 11, 2012, I have tried clicking on the various date options under format cells. When I looked up how to sort date, Excel shows just the format 2/11/2012. And when I did my mock up I used 2/11/2012 and had no problem.???

Report •

#5
April 16, 2013 at 11:45:53
✔ Best Answer
The only thing I can think of is your dates are getting stored as text. I found this link that explains what I'm talking about.

http://office.microsoft.com/en-us/e...

Let me know if this helps.


Report •

#6
April 16, 2013 at 12:16:06
I think this is the ticket!! Thanks so much for your help!!

Report •

#7
April 16, 2013 at 12:27:07
No Problem! Be sure to mark the best answer to set this thread as solved!

Report •

#8
April 16, 2013 at 12:29:01
Try this:

Select any empty cell.
Right-Click...Copy
Select your dates
Right-Click...Paste Special
Operation...Add - you should get a bunch of 5 digit numbers
Format cells as Date

Sometimes dates that are imported from external sources get stuck as text and will not let that format go no matter how hard you try. By "adding" a blank cell, you are essentially adding "0 days" to the text-dates. This forces Excel to recognize the contents as the number that Excel uses to internally store the date. Once it has been converted to it's internal "serial number" (the 5 digit number) that value can be formatted as a date.

See here to read about how Excel stores Dates and Times internally:

http://www.cpearson.com/excel/datet...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
April 16, 2013 at 13:01:48
A crude and simple way forward is to put an apostrophe in front of each entry. It will then be seen as text and not convert it to Excel date format at all. Another way to get around it is to use forward slashes instead of back slashes.

Always pop back and let us know the outcome - thanks


Report •

Ask Question