"h:mm" format, but 12-hour time, not 24-hour time?

  • Thread starter curiousgeorge408
  • Start date
C

curiousgeorge408

Ostensibly, I would like to use the format "h:mm", which does not
display AM or PM. But I want it to always display 12-hour time,
ignoring AM and PM, not 24-hour time.

Is there a format (standard or custom) that will do that
automagically, without my resorting to a formula?

(I am using Excel 2003.)

If I enter 10:00 in one cell and drag down, the time after 12:00 is
13:00. I want 1:00, just as I would get if I used the format "h:mm AM/
PM". (But of course, that includes the AM or PM suffix, which I do
not want.)

To get what I want, I have to manually change 13:00 to 1:00, then
start dragging down again.

That's a drag :).
 
J

Jon Peltier

I don't think it can be done with number formats alone. You'd have to
actually change the value in the cell so it's less than 0.5 (less than
12:00). And this may affect further calculations.

If I needed this display, I'd keep the "real" times in another column
outside of the display, and use a formula like

=IF(B3<=0.5,B3,B3-0.5)

- Jon
 
C

curiousgeorge408

On Sep 1, 6:05 pm, "Jon Peltier" <[email protected]>
wrote:
[ > > Ostensibly, I would like to use the format "h:mm", which
[ > > does not display AM or PM. But I want it to always
[ > > display 12-hour time, ignoring AM and PM, not 24-hour time.
I don't think it can be done with number formats alone.

Thanks. As I suspected. Just wanted to double check, in case I
missed something obvious.
 
D

David Biddulph

Agreed, or just =MOD(B3,0.5)
--
David Biddulph

Jon Peltier said:
I don't think it can be done with number formats alone. You'd have to
actually change the value in the cell so it's less than 0.5 (less than
12:00). And this may affect further calculations.

If I needed this display, I'd keep the "real" times in another column
outside of the display, and use a formula like

=IF(B3<=0.5,B3,B3-0.5)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
 
R

Rick Rothstein

I was originally going to suggest that formula too, but I didn't like how
12:00 got converted.<g>
 
M

MartinW

Hi George,

If you put 1:00 in a cell and then drag down 12 cells,
release the fill handle and then grab it again and drag
down as far as needed, hold down Ctrl, release the fill handle,
then release Ctrl.

You will have a repeating series of 1:00 to 12:00
which are actually all AM times.

Is that the sort of effect you are looking for?

HTH
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top