how to concatenate 12 hr time and the text "AM" or "PM"
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!
