1

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.

1
  • 3
    You will need a helper column in which to store the hour: =HOUR(A1) then sort on that column. – Scott Craner Oct 26 '18 at 16:46
5

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

=A2-FLOOR(A2,1)

enter image description here

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

enter image description here

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.

POC

Adjust cell references to suit your data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.