sorting date column oldest to newest

Copper Contributor

I imported a csv file of my connections from linkedin and I want to sort them by the date we connected.

Even though I mark the whole column (F) to be a date column, so they become date cells (aligned to the right and sort-able newest-to-old) some don't and remain aligned to the left and sort is only a-z,

Any help would be much appreciated, I've been trying for days!!!

16 Replies

This problem is common!

 

These dates that you think them are dates are not dates!

They are texts!

 

Please provide us with a sample of these text dates, to figure out how to parse them!

Here is a sample - I deleted a few name and email columns.

Thanks!

Celia,

 

use "Get and transform" (aka Power Query) to connect to the csv-file and this code to convert the date column.

 

let
    Source = Csv.Document(File.Contents("D:\Sample Barak_TL.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"Connected", type date}}, "en-US")
in
    #"Changed Type with Locale"

 

 

Thanks Lewin, but I don't understand...
Can you be very specific?

I'm a novice user and don't know Excel well at all.

 

Thanks!

 

When I opened the file on my device I didn't find any problem in the dates!

Because these dates are compatible with my local date format which is: M/d/yyyy.

 

FYI:

Dates in the Excel file must be compatible with the date format in the operating system, if the original format of the dates is different from the local date format, problems with dates will appear, they may be treated as text, or they may remain formatted as dates, but not as you think it.

 

Examples:

Local Date is: (M/d/yyyy)

Excel Date is: 15/12/2017

This date will be treated as text because there is no month 15!

 

Local Date is: (M/d/yyyy)

Excel Date is: 1/12/2017

This date will still formatted as Date in Excel, but not the date you may want.

You may think it 1/Dec/2017, but it actually 12/Jan/2017 as the local date format!

 

Please take this information into account.

 

You have to make the dates in your Excel compatible with your local date format, so please provide me with your local date format is it (M/d/yyyy), (d/M/yyyy), or something else, to provide you with the appropriate solution!

OMG! this makes so much sense! thank you.

I tried changing my date local and all I could find was
in The Advanced options, to tick the "Use 1904 date system" - that didn't help.

My local date is: dd/mm/yyyy

1904 date system option isn't related!

Go back to advanced options and uncheck its checkbox!

Local date settings are found in Control Panel >> Region and Language.

But I don't recommend you to change them!

 

To solve the problem follow these steps:

Step 1

Sort the column that contains the dates from oldest to newest in order to move the text dates to the bottom.

 

Step 2

Highlight the text dates as shown in the below screenshot, and keep them highlighted.

 

Text Dates.png

 

Step 3

Go to Data >> Data Tools >> Text to Columns.

 

Step 4

In Text to Columns window click Next twice.

 

Step 5

Select Date from the Column data format group box, and select the date format of text dates that you have previously highlighted., If its format is US format (MDY), select it and hit Finish as shown in the below screenshot:

 

Text Dates 2.png

Thanks Haytham!

This is what I got, close but not 100% - I still need to clean it up...

any suggestions how to take it from here?

 

I really appreciate your help - thanks again!

 

sample CSV dates.png

This little option is the cause of this problem, I forgot to tell you about it:

Text to Columns.png


You should have cleared this check box!

If you can undo the process, undo it, and take into account to clear this option.

 

If you can't, follow this workaround:

Next to the first cell contains 2017 use this function as shown in the below screenshot:

 

=DATE(YEAR,MONTH,DAY)

DATE Function.png

 

 

Then copy it down using the fill handle to get the same result in the above screenshot.

After that, highlight the range, copy it and paste it as values only to get rid of the formulas.

Then delete the previously separated values on multiple columns, and move the dates to the right place.

 

Hope that makes sense.

 

Done!
It worked but the cells are still text and not date format
so I can't sort them new --> old...

Any ideas?

Thanks,

Celia

sample CSV2.png

Highlight them and apply these steps, step by step:

 

 

Step 1

Go to Data >> Data Tools >> Text to Columns.

 

Step 2

In Text to Columns window click Next.

 

Step 3

Clear the check mark from the below option (You may find it already cleared)

Then click Next.

Text to Columns.png

 

Step 4

Select Date from the Column data format group box, and select the date format of text dates that you have previously highlighted., If its format is US format (MDY), select it and hit Finish as shown in the below screenshot:

 

Text Dates 2.png

YEY!!!!

 

I got it... THANK YOU SO MUCH Haytham.

For all your help and patience.

 

The only thing I was doing wrong was selecting the D/M/Y option instead of M/D/Y to match the rest of the sheet.

 

It's now working and I'm able to sort newest to oldest etc. 

Hooray!

Text to columns on the Data tab usually fixes that, just select date.

 

Also, if you've taken data out of MS Project. The date name "Tue" "Wed" etc is imported. It will fix it to actual dates by doing a find | replace "??? "

This really worked for me. Thanks for taking the effort to share.

Hi,

It's been a while since the topic, but the solution that I created may be useful for those who cannot solve the problem.

 

Dates in a column can be sorted easily from oldest to newest in other column by getting records from Adodb.Recordset :

rs.Open sorgu, con, 1, 1

Range("K3").CopyFromRecordset rs

 Excel_Vba_sort_dates_1.gif

 

Review this source

I have a much simpler solution. I still couldn't get option of 'newest to oldest' to appear in Sort, after following steps above to use 'Text to Column' on data tab, and setting number format in column to date dd/mm/yyyy.
So instead I changed date format to yyyy-mm-dd and then I could use 'sort ascending' in the sort function to get my dates from newest to oldest.