Remove AM/PM from "time" cell

Question

I am calculating all the hours/minutes/seconds of all calls made on our PABX system.

The cell has to be "time" format to display correctly. BUT, this adds an AM/PM at the end of the result as if it was the time. Whereas I just want it to display as HH:MM:SS

How do I remove the AM/PM?

Choose a format that does not include that, you want the format to only be `HH:MM:SS` and not `HH:MM:SS AM/PM` — Scott Craner, Mar 01 '17 at 17:52
Hahaha thanks @ScottCraner that was actually easier than I thought it would be! :) Thanks a lot for the feedback! — , Mar 01 '17 at 18:09

score 4 · Answer 1 · answered May 07 '17 at 18:33

Since you are already calculating time differences, use the TEXT function to format the time in hh:mm:ss format rather than changing the format for the cells.

=TEXT(A1,"hh:mm:ss")

In your case your calculation formula should be,

=TEXT(your_calc_formula,"hh:mm:ss")

score 1 · Answer 2 · answered Oct 01 '17 at 12:00

1

B1=MOD(A1-1,12)+1 A1 varies from 0 to 23 if a1=3 or 15 then B1 will be 3 if a1=0 or 12 then B1 will be 12

answered Oct 01 '17 at 12:00

Havlei

11
1

score 1 · Answer 3 · answered Feb 11 '20 at 17:55

Convert the time to a general value, and custom formatting to remove AM/PM.

Solution: 1. Change the cell format to "general", the time will become a number. For example 0:06:40 becomes 0.004375. 2. Add a "=" in front of the general value 3. Now use Command+1(for Mac) or Ctrl+1 (for PC) to apply custom format hh:mm:ss

I am often making time calculations for our company's call center and ran into the same problem. Even when I applied custom formatting hh:mm:ss, the cell still showed AM/PM. So this solution worked very well for my reports.

Example/Results

score 0 · Answer 4 · answered Sep 25 '20 at 15:07

0

It is not possible without AM-PM. You should use custom input time to do this work.

Use BootStrap TimePicker control to do this:

https://eonasdan.github.io/bootstrap-datetimepicker/

answered Sep 25 '20 at 15:07

Ayyaj Inamdar

41
7

Nancy Berntsen · Answer 5 · 2021-03-08T07:14:52.300

I have a very low-tech solution to this problem that does not involve any custom formatting or coding, macros, etc.

Use the standard formatting for time; i.e., [$-en-US]h:mm:ss AM/PM;@ This will result in time format like this: 11:00:00 AM or 2:45:00 PM. You can include seconds if you need them.)

Now format those cells where applicable so they go to the right rather than left or centered.

Next, create a solid square shape, and size it to hide the AM or PM.

Drag the square over the "AM" or "PM" and resize it so you don't see it.

Adapt the column or cell width as desired.

Once the square mask is in place, format the square with no outer line and change to fill style and color to match your cell color. In the attached image, you can see AM/PM. Next, I put a transparent, outlined mask over it. Then I remove the outline. Finally, I make it solid white like the rest of the cells. Then I copy and paste it over each AM and PM. Easy peasy. :) Screenshot of transitioning from AM/PM showing to masked

Remove AM/PM from "time" cell

5 Answers5