You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How do I stop Numbers sorting the earlier time after the later time?

I have a custom format that consists of hour:minute:second. No date, just time.


When I enter a time (e.g., 07:21:30) Numbers silently adds today's date to the cell, and uses that date for sorting.


So if I add a row with a cell value 07:21:30 today, and tomorrow add a row with a cell value of 07:18:00 tomorrow, Numbers will sort the earlier time after the later time because of the hidden day values in the cells.


How do I make it stop doing this?


[Re-Titled by Moderator]

Posted on Aug 29, 2022 1:45 PM

Reply
Question marked as Top-ranking reply

Posted on Aug 29, 2022 3:38 PM

It sounds like your custom format is actual a Date&Time format. There is no format I know of for durations that will pad a leading zero onto the hours. There are a few options. Three are below. I think the third is the least error prone and easiest but it isn't quite what you want.


First way:

If you format this column as text before entering any durations they will sort correctly as text. You will have to type the entire duration, including leading zeros and colons. 7:21:30 is not the same as 07:21:30 when sorting. If you will ever go into the hundreds of hours, all of them must have three digits for the hours (007:21:30, 108:29:32, 000:00:12, etc) to sort correctly.


If you have a few values already entered, you cannot simply reformat them as text. It might be easiest to delete them and enter them again after the column is text. If you have a lot of them entered already, a formula can be used to convert them into text "durations" in a temporary other column so you can copy/"paste formula results" to the original column.


As text, they are not durations or numeric. If you need them as durations for downstream functions, add a new column where you multiply the text "duration" by 1. That will convert them into durations. Use that column in formulas and then hide it.


Second way:

Format the column as duration before entering any data. Enter your durations into each cell as 7h21m30s. It will not pad a 0 to the left of the hours. You will get 7:21:30 not 07:21:30. Do not enter the durations using colons. If you do, Numbers will think they are dates, regardless of how you formatted the cell and that you are using 0:00:00 format for the durations. Stupid, yes, but that's how it is.


Third way

It seems like Numbers prefers the 7h 21m 30s format for durations. Use that format for the column. Enter the durations in that format (the spaces are not required for data entry). It will not mistake them as dates. It will sort them correctly. You can use them in formulas and functions. All around it is much easier.


3 replies
Question marked as Top-ranking reply

Aug 29, 2022 3:38 PM in response to ElViejo1939

It sounds like your custom format is actual a Date&Time format. There is no format I know of for durations that will pad a leading zero onto the hours. There are a few options. Three are below. I think the third is the least error prone and easiest but it isn't quite what you want.


First way:

If you format this column as text before entering any durations they will sort correctly as text. You will have to type the entire duration, including leading zeros and colons. 7:21:30 is not the same as 07:21:30 when sorting. If you will ever go into the hundreds of hours, all of them must have three digits for the hours (007:21:30, 108:29:32, 000:00:12, etc) to sort correctly.


If you have a few values already entered, you cannot simply reformat them as text. It might be easiest to delete them and enter them again after the column is text. If you have a lot of them entered already, a formula can be used to convert them into text "durations" in a temporary other column so you can copy/"paste formula results" to the original column.


As text, they are not durations or numeric. If you need them as durations for downstream functions, add a new column where you multiply the text "duration" by 1. That will convert them into durations. Use that column in formulas and then hide it.


Second way:

Format the column as duration before entering any data. Enter your durations into each cell as 7h21m30s. It will not pad a 0 to the left of the hours. You will get 7:21:30 not 07:21:30. Do not enter the durations using colons. If you do, Numbers will think they are dates, regardless of how you formatted the cell and that you are using 0:00:00 format for the durations. Stupid, yes, but that's how it is.


Third way

It seems like Numbers prefers the 7h 21m 30s format for durations. Use that format for the column. Enter the durations in that format (the spaces are not required for data entry). It will not mistake them as dates. It will sort them correctly. You can use them in formulas and functions. All around it is much easier.


Aug 29, 2022 3:59 PM in response to Badunit

Thanks. Yes, it's a date and time format. My workaround is to paste a constant dummy date before the time. So the custom format is 00:00:00 for h:m:s, with no date in the customization format bar. To make it work I have to enter:


1/1/11 07:21:30


Numbers suppresses the "1/1/11" in display, so it shows just


07:21:30


and even though Numbers uses it in the sort it has no effect because all the cells have the same (fake) date field.


Seems a bit peculiar for Numbers to autofill a default date, use it in calculations, but suppress its display when a custom format explicitly omits date. But there you are.

Aug 29, 2022 7:08 PM in response to ElViejo1939

One issue with using date&time is the time can never exceed 23:59:59. It is a time of day. If you exceed that, like entering 32:21:30, Numbers will override your formatting and give it an "automatic" duration format. 32:21:30 can't be a time of day so it assumes it is a duration. Then you'll have a mix of data types in the same column.


If your "durations" will never exceed that and want to enter just a time (letting the date be what it is) and be able to sort properly, you could create a second column with the formula =TIMEVALUE(your_date&time_column) . That will convert the time of day to a decimal value, ignoring the date. Set the text to white so it is invisible. Make the column narrow. Maybe put the word "SORT" in the header. Sort using that column.


How do I stop Numbers sorting the earlier time after the later time?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.