# Midnight Problem

#### n1lesh

##### Board Regular
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)

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### gaz_chops

##### Well-known Member
Because you have no date, excel sees them as the same day, therefore sorts them correctly.

#### n1lesh

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

#### n1lesh

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

#### Rick Rothstein

##### MrExcel MVP
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

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

#### n1lesh

##### Board Regular
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

##### Well-known Member
I think Rick may have an alternative solution if you answer his question.

#### n1lesh

##### Board Regular
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

##### Banned user
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.

Replies
4
Views
280
Replies
5
Views
219
Replies
0
Views
168
Replies
1
Views
177
Replies
4
Views
92

1,196,018
Messages
6,012,892
Members
441,738
Latest member
dataexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back