Hello, a question from an absolute amateur. I have a Calc spreadsheet file that was imported from a recording controller. It has 12 hour time in one column (eg 05:00:00), and the text “AM” or “PM” in a separate column right next to it. I had been sorting it for other work, now I can’t get it to sort right by date and time. The time is in 15 minute increments. The dates sort good, but the time is simply sorting in numerical order as the AM and PM are in a separate column. So 12:00:00 | AM ends up coming after 11:45:00 | AM instead of after 11:45:00 | PM. To solve this, I need to merge the 12hr time and the text “AM”, so that it reads “12:00:00 AM” in one column. Then it will automatically change the time format to 24 hr time which will sort correctly. I can do a merge on two cells, and say yes to moving the “contents of the hidden cell into the first cell”, then it comes out right. But I don’t want to do that a million times. And I can’t just block out all the data and do the merge as it puts everything into one cell. So, 1) Is there a “MERGE” command that can be put into a formula, so I can create a 3rd column, and paste in a formula which takes the two previous columns and merges them correctly, or 2) is there a way to do CONCATENATE on the time and AM/PM text (I have tried the simple CONCATENATE(A1&B1) but it doesn’t work, 5:00:00 | PM comes out as 0.208333), or any other ideas? Thanks!
Something like:
@mariosv: (See also my comment to the answer by @mark_t) This solution will only work in specific locales accepting AM/PM notation by default. In fact the TIMEVALUE function is “locale sensitive” but not “cell-numbers-locale sensitive”. How did you get the numeric A1 in this locale? (The contents in OQ’s first column migh be text from the beginning.)
This worked great…thanks so much!
@Lupp, if that notation it’s not fine then the user needs to rework a bit the formula. If A1 is a text then I think most of locales use colon as separator for times, if a number not conversion it’s needed.
If I correctly understand your described application I think you do not need to concatenate columns. If you use menu “Data”, “Sort” then you can set three different columns to use for sorting. Set the sort columns in the order of Date, AM/PM and then Time and this should sort the data as required.
@mark_t: The AM/PM gibberish is simply too silly to allow for such a solution:
12:30 PM is eralier than 1:30 PM!
Yes sorry, I missed the 12:30 PM issue.
Assuming the first column to contain text built from the first 8 places of time values formatted by “HH:MM:SS AM/PM” under a respective locale, a locale independent reconstruction of the correct time values as used by spreadsheets can be done this way:
=TIMEVALUE(IF(LEFT(A1;2)="12";SUBSTITUTE(A1;"12";"00";1);A1))+IF(B1="PM";1/2;0)
“Locale independent”, of course, based on the column contents being in characters as descibed (ASCII conforming).
The results used for sorting will work independent of the displayed format then.
Why do you want to concatenate it, when you can use a Format Code like the following on your column?
M/DD/YYYY H:MM:SS AM/PM
- Select your column
- Right click for the context menu and select
Format Cells... - In the format code put something like the text above…the
AM/PMpart will display the AM or PM bit in every row but the header in your selected column after you press OK.
Did you read the original question? Quoting from the question:
“…It {the sheet} has 12 hour time in one column (eg 05:00:00), and the text “AM” or “PM” in a separate column right next to it. …” (clarification in curly brackets and emphasis by @Lupp ).
A 12-hour-time in the first column does not contain the needed information to distinguish AM and PM.
(12:15 is always PM if the AM postfix is missing, and 1:00 through 11:59 will always be AM).
Alright sorry, you’re right…