Sorting by date

Lopsie96

New Member
Joined
Apr 27, 2016
Messages
6
Hi,

I have exported a file as a CSV that includes dates and other information. Upon export, the dates are not in chronological order and I cannot seem to get them sorted into chronological order. I don't know what I am doing wrong. I also do believe that the dates are stored as text but can't seem to understand how to get them converted to date format even though I have highlighted the column and formatted as a date. Can someone help??
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

joshman108

Active Member
Joined
Jul 6, 2016
Messages
310
In a separate column, you can use the DATE function to convert the text value to a date format (you will need to parse it out from the text, let me know if you need help - include sample data from your workbook). You should then be able to sort on this new date column.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,366
Office Version
365, 2010
Platform
Windows, Mobile
Try selecting the column, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked and click "Next", select General and click "Finish"

There is a date option at the last stage if your date needs further adjustment rather than just the General option (only specifying General to start with as you sometimes have to use the reverse m/d to what you think depending on your region and you haven't stated what your date format is).
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
I also do believe that the dates are stored as text but can't seem to understand how to get them converted to date format even though I have highlighted the column and formatted as a date.
Changing the format of a column of existing data will not convert the data, it just changes the format of the data (and date and other custom formats only apply to numeric data, not text data).

If the date column is already in a format which can be converted to a valid date, then you can simply select that column, go to the Data menu and select "Text to Columns", go to step 3 and choose the Date option with the proper date format, and click Finish. That will convert the Text entries to valid Dates.

If that does not work, please post a few sample of what these date entries look like.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
You are welcome.
Glad we were able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,224
Messages
5,443,189
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top