custom time format - 12 hr without am/pm

istril

Board Regular
Joined
Aug 15, 2008
Messages
109
I'd like my times to display as h:mm, but on a 12 hour format, WITHOUT the AM or PM trailing it. I fooled around with the custom time formats, but I'm having trouble doing this without it getting converted to military time.
 

istril

Board Regular
Joined
Aug 15, 2008
Messages
109
... Ok, Norie, I appreciate your attempts to help, and I do believe I've given examples. But I really would like someone else to help me if you still do not understand my request. I'm really not sure how to make it clearer.

How do I make the time value of 18:00 display as 6:00?

I just want to display times in a 12-hour format without the AM or PM. I just don't know how to explain that any clearer. I'm sorry.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi istril

There is no number format that can directly diplay 18:00 as 6:00, they are not, in fact, the same time.

You'll have to find some workaround that may suit you. For ex., 2 options:

1 -use in another cell a formula, like

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")

Notice that the result in the cell is a string, not a time value, and you lose the information if it is am/pm

2 - hide the am/pm part in a second line

You can format the cell with a format this way.´

In the Custom Format write

. h:mmAM/PM
. position the cursor between the h:mm and the AM/PM
. press CTRL-J

Format the cell with wrap text
This format sends the AM/PM part to a second line. By keeping the default row height, this second line is not visible and so it's like you have the format you wanted.

Notice that in this case you still have the time value in the cell, you lost no information.
 

grizz

Active Member
Joined
Jul 28, 2009
Messages
400
Did you try to format your cell with custom and scroll down to h:mm am/pm and click on this and then in type back space out the am / pm
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Just to clarify, you want to type into a cell in full 6:00 PM but display only 6:00
Yeah the only way would be to convert to text then use the last method to remove the Am/Pm part of it.
 

grizz

Active Member
Joined
Jul 28, 2009
Messages
400

ADVERTISEMENT

Also you can Format Cell / custom and type in 0\:00 and OK now if you put in 225 it will display as 2:25
 

pcason

New Member
Joined
Nov 13, 2012
Messages
1
Thank you so much for this! I have wasted hours trying to figure it out and option 1 worked like a charm for me.

Hi istril

There is no number format that can directly diplay 18:00 as 6:00, they are not, in fact, the same time.

You'll have to find some workaround that may suit you. For ex., 2 options:

1 -use in another cell a formula, like

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")

Notice that the result in the cell is a string, not a time value, and you lose the information if it is am/pm

2 - hide the am/pm part in a second line

You can format the cell with a format this way.´

In the Custom Format write

. h:mmAM/PM
. position the cursor between the h:mm and the AM/PM
. press CTRL-J

Format the cell with wrap text
This format sends the AM/PM part to a second line. By keeping the default row height, this second line is not visible and so it's like you have the format you wanted.

Notice that in this case you still have the time value in the cell, you lost no information.
 

dkmahan

New Member
Joined
Dec 21, 2012
Messages
3

ADVERTISEMENT

A third option would be to just adjust the time back by 12 hours with cell math if it is 13:00 or greater. You would now type in 6:00 PM or 18:00 and it would convert to 18:00 - 12:00 or 6:00 without the PM!

Cell formula =IF(A1<TIME(13,0,0),A1,A1-TIME(12,0,0))<TIME(13,0,0),A1,A1-TIME(12,0,0))

<TIME(13,0,0),A1,A1-TIME(12,0,0))< html>
 
Last edited:

dkmahan

New Member
Joined
Dec 21, 2012
Messages
3
The cell formula is "IF(A1<TIME(13,0,0),A1,A1-TIME(12,0,0))"<TIME(13,0,0),A1,A1-TIME(12,0,0))< html>
 

dkmahan

New Member
Joined
Dec 21, 2012
Messages
3
Sorry. I can't get my cell formula to display after the less than sign. I'll try putting the less than sign in quotes. Of course if the quotes display, you do not need the quotes around the actual less than sign:

IF(A1"<"TIME(13,0,0),A1,A1-TIME(12,0,0)).
 

prrclark

New Member
Joined
Dec 5, 2014
Messages
1
Sorry. I can't get my cell formula to display after the less than sign. I'll try putting the less than sign in quotes. Of course if the quotes display, you do not need the quotes around the actual less than sign:

IF(A1"<"TIME(13,0,0),A1,A1-TIME(12,0,0)).

This custom format was a solution for me:
0\:00
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,900
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top