Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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 :-). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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 :-). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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 :-). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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 :-). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"h:mm" format, but 12-hour time, not 24-hour time?
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 :-). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
0100 (24 hour) time - Which format?? | New Users to Excel | |||
display time in 12 hour format | Excel Worksheet Functions | |||
how to get "day/night" from a cell of time/hour | Excel Worksheet Functions | |||
24 hour time format help with 00:00 calculation | Excel Discussion (Misc queries) | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) |