Forum Discussion

ArianneC's avatar
ArianneC
Copper Contributor
Feb 10, 2025

Sorting dates in Excel from oldest to newest is not working

Hi all,

I would like to sort a date series in Excel, formatted as Mon Feb. 10, 2025 08:59 AM, from oldest to newest, but I can’t get it to work.

Every week, I have to download a list from a program that uses this date format (see screenshot). I want to sort it in order from oldest to newest, but when I try to do so, the dates get mixed up.

  1. Downloaded list:

2. I select the cells and want to order it from old to new, then it gives me this:

Does anybody has a solution for this? Thanks so much in advance!

 

Arianne

excel
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 11, 2025

    Alternatively add a helper column with 

    =VALUE(MID(SUBSTITUTE(B1:B3,".",""),4,18))

    where B1:B3 is the column with those dates in it

    Then use this helper column to sort or do calculations with.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Feb 10, 2025

    I'm guessing you are opening a CSV file. Don't open that csv file directly. Instead, save it. Then go to Excel and click Data, Get Data, From File, From text/CSV.

    Select that date column and select split column by delimiter. Choose space and select to split only by the first delimiter. This splits it into a column with just the day and a column with the remainder of the date. You can delete the day column now. If you're in luck, power query will then recognize the dates already.

    • ArianneC's avatar
      ArianneC
      Copper Contributor
      Feb 10, 2025

      Hi JK,

       

      Thanks for your reply. I am actually opening an Excel file and not a CSV file. I tried to split, as you said, and eventually it may work, but too much of a hassle as I have to download these files on a daily basis. I just have to find an easier solution 😅 But thanks!

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Feb 12, 2025

        If this is a daily download to the same folder, I would set up a query that uses Get Data, From File, From Folder. Click Transform and filter the list for the correct folder (it adds subfolders by default, you may not want that). Next, sort the list of files with the most recent at the top, and only keep the first row. Expand the data of the file that remains and then follow the date steps I mentioned before. Now all you need to do in future is refresh your query to get your latest download into something useful.

Resources