Unable to sort dates from oldest to newest

[blackystrat]

Hi


So here is the situation. I have an excel 2007 spreadsheet that has a column with dates that I need to sort from oldest to newest or vice versa.


Here's what I did so far:


1. Did Text-to-Column > Finish
2. Even after step #2, I was still unable to sort them from oldest to newest.
3. So I went to Text-to-Column > Next > Selected Date (DMY) > Finish
4. The column still wouldn't sort date wise. So I repeated the above process #4 and this time, I changed the date format to MDY and back again to DMY
5. This time I was getting the sort from Oldest to Newest option but all the dates didn't switch back to the same format. Some remained in mm/dd/yyyy while some changed to dd/mm/yyyy


The weird thing is that, if after I perform step #2 and copy the column to a new sheet, I would immediately get the sort datewise option right away, but it isn't happening on the existing sheet.

Sample sheet attached at - datessample.xlsx - Send Files Online - TempSend.com


Kindly advise where I am going wrong


Hope I was able to explain my situation properly

 

[blackystrat]

Update - So Office 2013 didn't help either. So while formatting with Text to Column, I changed the date format to MDY which actually made the dates dd/mm/yyyy (I guess excel wasn't able to figure out which one is the month and which one is the date) and later manually custom formatted the cells to mm/dd/yyyy

So the issue stands solved as of now. Thank you all very much for your help

 

[FDibbins]

Based on post 10...

if 12/04/1990 is converting to 14-10-2097 (formatted as dd-mm-yyyy), that equates to 72242 (which could equate to 5:20:17 PM). This is telling me that you have more than just 12/01/1990 - perhaps it includes time?
(and it wont make a difference if you are using 2010 or 2013)

 

[jtakw]

You're welcome, glad you found a solution.

 

[Vijaykumar Shetye India]

Dear blackystrat,
I think the the values of the month and day have got interchanged.
If this is true, the try the below formula, and format the cells as date.
=DATE(YEAR(A2),DAY(A2),MONTH(A2))
It will convert day to month and month to day.

Vijaykumar Shetye, Goa, India