Excel can't properly sort by time

Question

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.

Answer 1

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

Hi.   I have no idea what the values are, but what I mean about looking at the underlying values is this:

It looks like you "may" be doing time calculations.  For my demo,

3:00:00  sorts ascending before 2:00:00

This is because the actual values are:

.125

1.083333

10 pm + 4 hours might be 1.08333, and is "larger" then the 3 am value of .125

You are just formatting the output to display time.  I have no idea, but it's just a guess.

Answer 2

Thanks for feedback,

I don't know what happened, but now it seems to work (I do 2-level sorting: 1st - Date, 2nd - Heure)

I formatted column Date as "Date" and column Heure as "Time".

But again, for Date only available option for sorting is A to Z

and for Heure - Smallest to Largest.

Are these options correct for "Date" and "Time" types ?

Thanks

Answer 3

Well ... this sorting option in Excel is the most awkward thing I've ever seen implemented in software. Actually it works "when it want": sometimes it works, sometimes NO. As they say "Why make things simple when one can make them complicated ?".

Once more, here is my setup:

• 5 columns (first two columns are evolved in sorting procedure)
• 1st column: data type(I want it be in format DD.MM.YYYY)
• 2nd column: time type(I want it be in format hh:mm)

What should I do with my sheet in order this scenario works reliably ?

Thanks in advance.

Pavel