Convert custom date format (saved as text) so that you can sort the data
I have downloaded a .CSV file with some data. Specifically the file is from LinkedIn.
LinkedIn have decided to use some custom date format scheme: 2/7/18, 1:07 PM
My mission: I want to sort the data, so that the rows with the newest date is in top.
My problem: I cannot sort the data correctly, because Apple Numbers do not recognise the format
My idea: I want to tell Apple Numbers how they can understand the dateformat, so they it can sort it properly.
What I have tried:
Create custom dataformat
Marking the columns where Date is in. Then clicked Cell -> Data Format -> Create Custom Dataformat. I have tried to replicate the format exactly. This fails to do the job. I guess it expects a "date" column, and then it can "reformat" it to another format. However, the column I'm trying to convert is a "text" column. So I guess this tool won't do the job.
Final resort
In case theres no tool for this, the only complicated way I can think of is:
- Create 3 new columns: Date, Time and DateTime
- Split the Date column by comma, and part it into Date and Time.
- Merge Date and Time into DateTime and make sure it's formatted as DATE
- Sort the columns
