Midnight Problem

[n1lesh]

I have the following times in a worksheet:

22:10 45
23:34 46
00:05 39
01:45 67
02:10 98
01:45 76
23:48 28
02:45 66

I have two problems:
how do I get excel to sort the list in order from the earliest time which is 22:10
to the latest time which is 02:45
excel always does from midnight first which is not what I want.

when I manually put the times into another worksheet the corresponding time cells
have a value next to them and when I do a vlookup to find the value it gives me a totally wrong answer
=vlookup("<00:00",A1:B10,2)

 

[gaz_chops]

Because you have no date, excel sees them as the same day, therefore sorts them correctly.

Can you add the date?

 

[n1lesh]

I cant add the date to the data set I have as the data set is massive

 

[n1lesh]

if I could add the date how would I go about doing it

 

[Rick Rothstein]

how do I get excel to sort the list in order from the earliest time which is 22:10

If midnight is not considered the earliest possible start time, then what do you consider the earliest possible start time to be (we need to know that in order to know where the sort should begin looking at)?

 

[n1lesh]

took your advice on adding the date with a few times will let you know how I get on

 

[n1lesh]

adding the date with the text function still does let me sort the data or do a vlookup on the data what would be the best way to add a date to the times

 

[gaz_chops]

I think Rick may have an alternative solution if you answer his question.

 

[n1lesh]

I need the times sorted anything before midnight goes first then anything after midnight goes after it e.g
22:10
23:29
00:01
00:10
01:10
02:10
02:29
03:10

 

[joeu2004]

adding the date with the text function still does let me sort the data or do a vlookup on the data
what would be the best way to add a date to the times

Do not add a date with the TEXT function. Instead, add it numerically. In Excel, dates are represented by integers and time of day is represented by a decimal fraction.

Moreover, for your purposes, you might not need to add a real date. Simply add 1 to times that you consider to be after midnight.

You can format the cell with Custom hh:mm if you just want to show the time of day.

I need the times sorted anything before midnight goes first then anything after midnight goes after it e.g
22:10
23:29
00:01
00:10
01:10
02:10
02:29
03:10

First, I presume that your times of day are actually stored as numbers, not text. You should confirm that by creating a parallel column of formulas of the form =ISTEXT(A1), and ensure that it is always FALSE. For example, =COUNTIF(A1:A1000,TRUE) should return zero.

If you copy-and-pasted the column of times from another application (e.g. a webpage), it is very possible that the times are stored as text because they include extraneous characters.

Second, you need to decide what is the latest time of day after midnight that you consider to be part of the same day. For example, looking at 03:10, it is not clear whether you consider that to be after midnight or before.

Previously, you wrote "the latest time [...] is 02:45". It is unclear whether you referring to your entire list of times or to just the example.

But using that as an example, enter the following formula into a parallel column:

=A1+(A1<=TIME(2,45,0))

Again, you can format the column as Custom hh:mm to display only the time of day.

Use that column for your lookup value. Or copy the parallel column and paste-value over the original column, if you wish; then you can delete the parallel column.

You must also add 1 to any time in your VLOOKUP table that you consider to be after midnight. For example, replace 1:10 with =1+"1:10" or =1+TIME(1,10,0). Then you can sort the VLOOKUP table in ascending order.