custom time format - 12 hr without am/pm

[istril]

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.

 

[Rick Rothstein]

Shucks, I am trying to write train schedules and need the cell size to be minimized so i can fit tons of info on one page.

You can save a little bit of extra space (the width of the letter M) by Custom Formatting the cells using this type pattern...

h:mm a/p

for the format pattern (make sure to use a small "a" and "m"). First, though, you might need to clear the existing format as I had trouble imposing this new format on top of the one that was already there (the a/p seemed to conflict with the existing AM/PM). Don't forget to put the Line Feed character (CTRL+J) back (in front of, not behind, the space).


EDIT NOTE
------------------------
I just noticed that you can remove the space (and save a little bit more room) as the Line Feed (CTRL+J) character works in place of the space! The overall saving in the width of the cell was 19 pixels on my monitor.

 

[pgc01]

One question though, my "Public" worksheet when using this method displays 1:30 pm as 01:30. Do you know a good way to drop the zero's from 1-9?

Instead of hh:mm use h:mm

 

[pgc01]

P. S. for ex.:

=TRIM(LEFT(TEXT(A1,"h:mm AM/PM"),5))

 

[oldbrewer]

													01:00	100
													02:00	200
				problem statement - make 18:00 typed in as 18:00 appear as 600				03:00	300
													04:00	400
				timetable for typing								05:00	500
													06:00	600
				18:00									07:00	700
													08:00	800
													09:00	900
													10:00	1000
													11:00	1100
													12:00	1200
													13:00	100
				timetable for actual use							14:00	200
													15:00	300
				600									16:00	400
													17:00	500
				=VLOOKUP($E$8,mytable,2)							18:00	600
													19:00	700
													20:00	800
													21:00	900
													22:00	1000
													23:00	1100
													00:00	1200
		you could make a table from 00:01 to 23:59
		and define thus how you want any time to display
		00:19 would be 019 I guess
													the above table is named mytable

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Rick Rothstein]

problem statement - make 18:00 typed in as 18:00 appear as 600

Where did you see this at? Note that this thread restarted at Message #24 .

 

[oldbrewer]

Rick - I sometimes state what the problem is imho where there is some lack of clarity. I only joined in when I saw the incredible number of views.

 

[oldbrewer]

I understood OP was making a timetable so 130 as text is acceptable and 130 can be used as either am or pm since the timetable provides the context.............

 

[smileystv]

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

dkmahan this is the best and most awesomestest solution.

GOOD SHOW!

 

[FallonRayArt]

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.

Great answer! Thank you. Option 2 worked great for my needs. Keeping the information as a time value is very handy.