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.
 
... 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.
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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)).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top