Dec 03 2017 10:22 AM
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!!!
Dec 03 2017 10:51 AM
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!
Dec 03 2017 11:24 AM
Here is a sample - I deleted a few name and email columns.
Thanks!
Dec 03 2017 12:22 PM - edited Dec 03 2017 12:24 PM
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"
Dec 03 2017 12:40 PM
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!
Dec 03 2017 08:42 PM
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!
Dec 03 2017 10:43 PM
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
Dec 04 2017 06:39 AM - edited Dec 04 2017 09:36 AM
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.
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:
Dec 05 2017 12:16 AM
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!
Dec 05 2017 07:40 AM
This little option is the cause of this problem, I forgot to tell you about it:
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)
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.
Dec 05 2017 11:06 AM
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
Dec 05 2017 11:44 AM
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.
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:
Dec 06 2017 05:48 AM
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!
Dec 06 2017 07:07 AM
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 "??? "
Sep 08 2019 09:43 PM
Nov 25 2021 01:36 PM
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
Apr 20 2022 04:58 AM