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?

Solution 1

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

Solution 2

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

Solution 3

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

Comments

[Scott Craner]

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]

Actually now that the photo is loaded you want [HH]:MM:SS

[Admin]

Hahaha thanks @ScottCraner that was actually easier than I thought it would be! :) Thanks a lot for the feedback!