Share via

Excel can't properly sort by time

Anonymous
2016-03-06T20:59:57+00:00

Hello,

Excel can't properly sort by time - in the screenshot below the marked item isn't at correct place.

Sorting sequence: 1st - Date, 2nd - Heure.

Help, please.

Thanks

Pavel.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-03-06T22:54:11+00:00

    Hello Pavel,

    Welcome to Microsoft Community and thank you for posting your query.

    Few more details would help us to provide a better response.

    1. Does this issue occurs while working in other Excel file?
    2. Are you using multi-level sorting in Excel file?

    If yes, I suggest you to check if any multilevel sorting is applied. To do so, select the data that is to be sorted. Click on Data> Sort> and check if any multi-level sorting is applied.

     Hope this helps. Please post back with the result, we would be glad to assist you further.

    Thank you.

    0 comments
  2. Anonymous
    2016-03-06T23:15:20+00:00

    Yes,

    I've copied 2 first columns in the new file and tried sorting there.

    The result is exactly the same.

    Yes I use multi level sorting:

    • 1st level - column Date
    • 2nd level - column Heure

    I think Excel can't properly recognize time format: instead of Oldest-to-Newest it propose for Heure only Smallest-to-Largest.

    Regards

    0 comments
  3. Anonymous
    2016-03-07T00:14:34+00:00

    In a sort by "Date",  Order of "A to Z" is not an option in Excel 2016 when the data is a Date.    "A to Z" is only available when Excel sees that the date is Text!

    Note that as text:

    12.1.2016

    Will come before

    6.1.2016

    Because the "Text" is sorted on the first digit.

    I would also look at the time values of 00:55 formatted as general.

    You may have a value that is greater than 1 (crosses midnight) when you may be expecting values 0-1.

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2016-03-07T06:22:02+00:00

    Yes,

    Before it didn't work even with "Date". Then I searched for a solution and found this one:

    http://superuser.com/questions/817110/unable-to-get-excel-to-recognise-date-in-column

    After applying this solution sorting by "Date" works.

    Unfortunately I didn't find something similar for "Time".

    Thanks

    2 people found this answer helpful.
    0 comments
  5. Anonymous
    2016-03-07T09:40:02+00:00

    Just an observation, the rows following 07.03.2016 00:55 do sort properly 

    08.03.2016 00:00:00

    08.03.2016 10:50:00

    Maybe the row in question is text?

    0 comments