Sorting time with 00:00 - 06:00 last possible?

[radekrat]

Guys - Is there a way to sort through time to make sure that values after midnight till 4am are shown last?

I copied a lot of arrival times into a sheet and sorted to find out that some of the times were changed to "01/01/1900 01:15:00" and were pushed to the end of the queue.

I have no idea how this happened for some but not the others and if there is a way to ensure when sorting time - anything after 00:00 - 04:00 is at the end of the list?

Any tips?

[popipipo]

Upload your excelfile here to see it.

[6StringJazzer]

Since 0:00 - 04:00 are the earliest times, there is no easy way to make them last in a sort, if all times have the same date (or no date).

What are these times for? Is this times only with no date? One possibility is to contrive your times from 0:00 to 4:00 to have a date of 1/2/1900, then they will be last in the sort order.

Another way is to create a "helper" column that modifies the times as described above and sort on that column.

Hard to give you more detail with the high-level description in your post. A sample file would also help.

[radekrat]

Yeah those are just arrival times. In some magical way when i pressed sort - some of them got the data attached and ended up at the end of the queue "01/01/1900". I wonder how that happened.

I have no idea how can i add date to the times between 00:00 - 04:00 unless i manually go in edit every single cell to add it.

[MrShorty]

How exactly do you interact with this sheet?

The key thing to understand, IMO, is that dates and times are just numbers. Dates are integers counting from 0 Jan 1900 (or 31 Dec 1899 for other spreadsheets that have learned how to count dates backwards). Times are the fractional parts of a day. So midnight is the fraction n.0. 04:00 is 4/24 or 1/6 of a day n.166666666..... 06:00 is 1/4 of a day n.25. When spreadsheets store time without date information, it is usually a number between 0 and 1. In order to get midnight to 04:00 or 06:00 to show up after later times, you need to get those times to be larger than 1. 1.25 is 06:00 of 1 Jan 1900, and will sort later than 0.25 which is 06:00 of 0 Jan 1900.

Once we understand how you interact with this sheet, then we can talk about ways to "add 1" to times are after midnight so that they will sort at the bottom of the list rather than the top of the list.

[radekrat]

MrShorty thank you - it makes sense now.
I have attached sample file - so since the arrival time is used elsewhere i need to paste it as text, change into times and sort
Now after your post i have noticed that some of the times after midnight will have 1 in the front and some would not for some reason.
Those with 1.x will be sorted at the bottom as it would add the date to it when converting number to time.

What could be causing this?

The formula would basically pick up schedule departure time or adhoc time and add run time to return arrival time. the data needs to be copied as text later into a different spreadsheet when it is used to create a simple report.

[sandy666]

some of values are formatted as date
some contains space(s) in front of
why there are blank rows between values?
so maybe fix your example

[MrShorty]

I'm still not sure I follow exactly how you are interacting with this sheet.

As Sandy666 notes, there are a few times/numbers stored as text intermingled with your entries in A and C. If A and C represent your "input" values, there could be value in exploring how those values are entered/imported into the spreadsheet so that they are correctly entered/imported as times/numbers.

It then appears that you are adding A and C together in column D (since B is empty for all examples, there is no example of adding A and B). This formula looks correct, and appears to be calculating correctly for all given examples. I see no errors. I would be concerned about the times/numbers stored as text. If you do as Sandy666 suggests and clean up the input data, those concerns should be alleviated.

Now after your post i have noticed that some of the times after midnight will have 1 in the front and some would not for some reason.

Can you show an example where you would expect the sum be greater than 1, but Excel is calculating a value less than 1? As I spot check column D, all the sums look correct, based on the values in columns A and C.

In row 12, for example, the value in A12 is 0.99931 (23:59 when formatted as time of day), and C12 is 0.05208 (30 minutes when formatted as elapsed minutes). Add these two up and you correctly get 1.05139 (1:14 when formatted as time of day, but the undisplayed day is now 1 Jan instead of 0 Jan).

In row 27, the value in A12 is 0.04167 (1:00 when formatted as time of day), and C12 is 0.05208 (30 minutes when formatted as elapsed minutes). Add these two up, and you correctly get 0.0625 (1:30 in the morning when formatted as time of day. Remember this is 0 Jan 1900, but the date is undisplayed).

Row 50 is a curious example. The value in A50 is 16 (0:00 when formatted as time of day, but the undisplayed date is 16 Jan 1900). Add 0.02803 (30 minutes), and you correctly get 16.02803 (0:30 when formatted as time of day, but remember that the date here is 16 Jan). Perhaps another example of the need for "cleaning up the data?"

As near as I can tell, everything is calculating correctly. How do you want these to calculate differently?

I'm not sure I understand the need to "copy as text then convert back to number/time" step. It seems superfluous to me.

[sandy666]

by the way, it might be there duration not time?
sometimes you have to distinguish between duration and. time

[radekrat]

Hi
Apologies if I am not able to communicate the issue clearly
So after closer analysis and your guidance - any time greater than 00:00 is going to end up on the beginning of the sort as a small number. this is due to it being converted into a small value of 0.01 and more - examples row 27

If I am adding travel time to a time pre 00:00 - it will be converted into 1.01 number and will end up on the end of the sort. examples row 99, 50 , 12

in this way 1:00am can end up at the end or beginning of the sort depending on the time the travel started.

Someone did an INDEX MATCH formula on all of the data to display only the runs that were not cancelled on another sheet and this is where i am extracting this data, every cell is formulised so need to copy as values or text in order to be able to sort - this is the reason.

[MrShorty]

in this way 1:00am can end up at the end or beginning of the sort depending on the time the travel started.

That is correct. Is that what you want to have happen, or is this an incorrect result?

Someone did an INDEX MATCH formula on all of the data to display only the runs that were not cancelled on another sheet and this is where i am extracting this data, every cell is formulised so need to copy as values or text in order to be able to sort - this is the reason.

I know that sometimes I find it easier to take a set of results and copy/paste as values. Would this be a situation where the SORT() function could be useful on the "formulized" data rather than copy/paste as values?