Ask Your Question

Calc: Sort on formatted time column

asked 2017-05-11 21:21:06 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a spreadsheet with separated date and time columns. The time columns are in format "H:MM:SS AM/PM" (e.g. "1:23:45 AM". I have formatted the cells correctly (highlight the time column, Format > Cells... > Numbers tab > Category: Time, Format code "H:MM:SS AM/PM")

The trouble comes when I try to sort by this column. It's not sorting chronologically, it's stacking all the AMs with the PMs. Is there a way I can make this work?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-11 22:14:33 +0100

Lupp gravatar image

updated 2017-05-11 22:17:27 +0100

If you can't sort time values or date values correctly, you can bet that they are actually texts instead of numeric values.
Where did you get the values from? How did you enter them?

I suppose you didn't just get AM and PM values mixed up, but also 12:30 PM is sorted in as later than 1:30 PM.
If the time format the texts are converted to (making text) is known to the locale of your sheet cells you can use the tool 'Data'>'Text to Columns...'. another way is to use the formula =TIMEVALUE(Reference) in an empty column and then to 'Copy'/'Paste Special...' (values only) into the places you want them. The numeric values can then be displayed in any 'Numbers' format.

After that you can apply a format anew. You may want to consider using an internationally standardised format like "HH:MM:SS" or "HH:MM" and abandon the misleading AM/PM. In international formats times sort correctly even if they are texts.

edit flag offensive delete link more


Yep, "Text to Columns" was the step I was missing. Thanks!

Bungler gravatar imageBungler ( 2017-05-11 23:15:12 +0100 )edit

FYI, the data was an import from a CSV, so definitely started out as text, but I was apparently under the mistaken impression that formatting the column was the only thing needed to do.

Bungler gravatar imageBungler ( 2017-05-11 23:16:38 +0100 )edit

@BuBungler: That's one of the most common errors. Actually the appropriate setting should be made in the import dialog. (Once again a suggestion: Abandon AM/PM.)

Lupp gravatar imageLupp ( 2017-05-11 23:21:18 +0100 )edit

I'd love to, actually, and I'd love to get full UTC time stamps, but, alas, I'm just the data receiver, not the provider :)

Bungler gravatar imageBungler ( 2017-05-11 23:23:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-11 21:21:06 +0100

Seen: 289 times

Last updated: May 11 '17