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.