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

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
_______


wrote in message
...
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 :-).

On Sep 1, 6:05*pm, "Jon Peltier"
wrote:
[ 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.

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

"Jon Peltier" wrote in message
...
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
_______


wrote in message
...
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 :-).

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

--
Rick (MVP - Excel)


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Agreed, or just =MOD(B3,0.5)
--
David Biddulph

"Jon Peltier" wrote in message
...
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
_______


wrote in message
...
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 :-).

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



wrote in message
...
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 :-).