How to sort dd/mm/yyyy hh:mm by hours instead of date?

Question

I would like to take my current dd/mm/they hh:mm and sort by hours. At the moment it is sorted by date, however I would like to sort with hour going from earliest to latest in the day regardless of the date. Any help much appreciated.

Answer 1

You have to create a new surrogate column that contains the time portion:

=A2-FLOOR(A2,1)

and then you can sort by this column (you can even hide the column later if you wish):

Answer 2

Since Excel stores the date time value as the integer representing the number of days since 1900/01/01 (that day being day 1) and time as a decimal representing percentage of the day, you want to sort by the decimal value. The following formula performs array like operations and therefore you want to avoid using full column references within the AGGREGATE() part of the formula.

=AGGREGATE(15,6,MOD($B$2:$B$11,1),ROW(A1))

MOD will return the remainder after being divide by a number. If you divide your date time value by 1, only the decimal will be returned which is your time.

AGGREGATE function 15 will will sort a list from smallest to largest. The 6 tells aggregate to exclude/ignore any error results. The MOD(range) part is doing its job over a range of cells, so you will have a list of cells when MOD is finished and aggregate will sort them as previously mentioned. ROW(A1) will evaluate to 1 telling aggregate to return the fist number in the list. As the formula is copied down, the cell reference will increase by a row and you get 2, 3, 4, etc as you copied down. In other words your sorted list.

Adjust cell references to suit your data.