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.

 

[maeyks]

Hi,

What if I want to put in 7:00 pm, but I don't need to enter 19:00? I will just type 7:00.
Thank you.

 

[freddie65]

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

Hi there

I want to add the number of hours to a cell that is formatted to display the time using the format 0\:00. How do I do this so that the result is dispayed in time format?

Example:

Cell with hours is formatted as a number at present.

Cell A = 2 hours formatted as a number at present.
Cell B displays the times e.g. 1:30 using format 0\:00
Call C I want to display the time with the hours added, so 3:30

Thanks

 

[serdar]

Yes, as do I. That is exactly my problem. How do you make the VALUE of 18:00 hours APPEAR as 6:00?

If 18:00 in A1 cell, you can write:

=MOD(A1,0.5)

 

[metradispatcher]

Hi istril

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.

Option 2 is what I am trying to use as I still need the cell to read that it is AM or PM but I need just the numbers to be visible (example : 13:30 needs to read 1:30)

When I try option 2, i get #### and wrapping text does not seem to do anything. If I open the width of the cell so there is excessive extra room (2x wider than the cell needs to be), it will display 1:30 as i hope. But no matter what I try, I cannot seem to eliminate this excessive space without the information displaying ####

Thanks for your post. Any pointers?

 

[pgc01]

Hi

Unfortunately with this format you'll have this extra space.

To have a smaller column width you could use an auxiliary column where you'd display the time as text, like:

=LEFT(TEXT(A1,"hh:mmAM/PM"),5)

and hide the source column. In this case, however, you should always refer to the hidden column when you need to use the time value.

Another way would be to use a custom literal format for the time, but that would only be practical for a small number or fixed values or you'd have to use code.

 

[Rick Rothstein]

When I try option 2, i get #### and wrapping text does not seem to do anything. If I open the width of the cell so there is excessive extra room (2x wider than the cell needs to be), it will display 1:30 as i hope. But no matter what I try, I cannot seem to eliminate this excessive space without the information displaying ####

You can kind of minimize the sense of the extra space by center aligning the cell horizontally... the column will still need to be as wide, but the spacing will not look as bad as when right aligned.

 

[pgc01]

You can kind of minimize the sense of the extra space by center aligning the cell horizontally... the column will still need to be as wide, but the spacing will not look as bad as when right aligned.

Good point, Rick!

 

[metradispatcher]

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.

Unfortunately, i am bound by a requirement that PM schedules be bold, which I was hoping to conditionally format PM times to be bold so it would happen automatically. This is also why I need the PM time in the cell although need the letters themselves to be absent for space savings. The other unfortunate requirement i have is to not use a 24 hour clock so the general public can understand it (*facepalm*).

I'll keep looking for a solution. Using equations would make this process so much easier and more accurate.

Thanks

 

[pgc01]

Unfortunately, i am bound by a requirement that PM schedules be bold, which I was hoping to conditionally format PM times to be bold so it would happen automatically. This is also why I need the PM time in the cell although need the letters themselves to be absent for space savings. The other unfortunate requirement i have is to not use a 24 hour clock so the general public can understand it (*facepalm*).

In that case can you not use the auxiliary column, that you hide, like I posted?

You can always update the source (hidden) column, and your schedule will update automatically.

 

[metradispatcher]

In that case can you not use the auxiliary column, that you hide, like I posted?

You can always update the source (hidden) column, and your schedule will update automatically.

Ah, just tried this solution successfully! I need to read ALL replies before I post, i somehow overlooked this. sorry!

I will be making a "Data" worksheet and a "Public" worksheet that pulls the information from the auxiliary data worksheet accordingly. 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?

Thanks for your help!