sorting time format

[Guerilla]

Hello,

I have a spreadsheet which contains arrival and departure times of
vehicles. The format of this is "0100" and not 01:00. This to speed
up input. However, when sorting the data I need to start the column
at 0600 (morning) and run to 0559. When I sort the data at the
moment, I get all of the 0000 and so on until 2359, and I have to
manually move the 0000 - 0559 to the end of the dataset. How can I
get Excel to sort the data starting at 0600 instead of 0000?

Regards,

Matt

 

[Pete_UK]

It's not clear if the times are entered as numbers with the cells
formatted to show leading zeroes, or if they are entered as text.
However, you can introduce this formula in a helper column:

=IF(VALUE(A1)<600,1,0)

assuming your "time" data starts in cell A1. Copy this formula down
and include this helper column in your sort range, sorting on this
field first then on your time field. This should drop all the times
before 06:00 down to the bottom of your sorted list. You can get rid
of the helper column after you have sorted.

Hope this helps.

Pete