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?
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.
Yep, “Text to Columns” was the step I was missing. Thanks!
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.
@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.)
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 