Forum Discussion

Copper Contributor

Dec 03, 2017

sorting date column oldest to newest

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 (align...

excel

Celia Calo

Copper Contributor

Dec 04, 2017

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

Haytham Amairah

Silver Contributor

Dec 04, 2017

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:

AmarCheema
Copper Contributor
Sep 09, 2019
This really worked for me. Thanks for taking the effort to share.
Celia Calo
Copper Contributor
Dec 05, 2017
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!
- Haytham Amairah
  Silver Contributor
  Dec 05, 2017
  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 https://support.office.com/en-us/article/Fill-a-formula-down-into-adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6 to get the same result in the above screenshot.
  
  After that, highlight the range, https://support.office.com/en-us/article/Paste-values-not-formulas-12687B4D-C79F-4137-B0CC-947C229C55B9
  
  Then delete the previously separated values on multiple columns, and move the dates to the right place.
  
  Hope that makes sense.
  
  DATE Function.xlsx10 KB
  - Celia Calo
    Copper Contributor
    Dec 05, 2017
    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