Ask Your Question

how to concatenate 12 hr time and the text "AM" or "PM"

asked 2016-07-27 18:23:18 +0200

this post is marked as community wiki

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

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!

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2016-07-27 23:46:42 +0200

m.a.riosv gravatar image

Something like:

image description

edit flag offensive delete link more


@m.a.riosv: (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.)

Lupp gravatar imageLupp ( 2016-07-28 10:34:09 +0200 )edit

This worked great...thanks so much!

KingProcessTech gravatar imageKingProcessTech ( 2016-07-28 14:04:03 +0200 )edit

@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.

m.a.riosv gravatar imagem.a.riosv ( 2019-03-22 09:39:08 +0200 )edit

answered 2016-07-28 03:23:45 +0200

mark_t gravatar image

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.

edit flag offensive delete link more


@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!

Lupp gravatar imageLupp ( 2016-07-28 09:29:48 +0200 )edit

Yes sorry, I missed the 12:30 PM issue.

mark_t gravatar imagemark_t ( 2016-07-28 17:30:45 +0200 )edit

answered 2016-07-28 11:15:12 +0200

Lupp gravatar image

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:
"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.

edit flag offensive delete link more

answered 2018-03-03 19:32:26 +0200

leeand00 gravatar image

Why do you want to concatenate it, when you can use a Format Code like the following on your column?

  1. Select your column
  2. Right click for the context menu and select Format Cells...
  3. In the format code put something like the text above...the AM/PM part will display the AM or PM bit in every row but the header in your selected column after you press OK.
edit flag offensive delete link more



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).

Lupp gravatar imageLupp ( 2018-03-03 23:22:43 +0200 )edit

Alright sorry, you're right...

leeand00 gravatar imageleeand00 ( 2018-03-09 13:43:10 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2016-07-27 18:23:18 +0200

Seen: 6,719 times

Last updated: Mar 03 '18