Just want hours and minutes not am and pm

**excelinator** · 05-08-2010, 12:15 PM

I am trying to enter just raw hours and minutes into excel. I enter say 1:00 with the cell formatting h:mm, the formula bar still shows 1:00:00 AM. I don't want the AM or PM. Thanks for your help in advance.

**royUK** · 05-08-2010, 12:24 PM

Use a custom format hh:mm

Format menu -> Cells -> Number -> Custom

**martindwilson** · 05-08-2010, 12:25 PM

the fact it shows am/pm is that it matches your system time which is not in your case set to 24 hour clock you need to change the time in regional settings to HH:mm:ss its probably hh:mm:ss tt at the moment

**excelinator** · 05-08-2010, 12:41 PM

I want to be able to be able add multiple times in terms of hours and minutes. For example, I want to add 1h 25m, 12h 5m to arrive at 13h 30m or 13:30. I tried actually doing it as it is showing (in the formula bar) right now and it works. This seems counter-intuitive because if i add 1:25 am and 12:05 am I would get a time like 2:30pm or something else wierd, but excel doesn't do that. Nevertheless, its doing what I want even though logically I think it shouldn't.

**excelinator** · 05-08-2010, 12:46 PM

Actually I take that back, if I add 24:05 to 1:25, excel converts 24:05 to "24:05:00 PM" and the values will not add together to get 25:30 or 25 hours 30min

**daddylonglegs** · 05-08-2010, 12:48 PM

If you sum the times and custom format the result cell with this format

[h]:mm

then that will display hours over 24

**shg** · 05-08-2010, 12:50 PM

Does for me:

      --A-- ----B-----
  1   24:05           
  2    1:25           
  3   25:30 A3: =A1+A2

All the cells are formatted as [h]:mm

**excelinator** · 05-08-2010, 12:55 PM

I have all cells involved in the summation in h:mm format.

I enter in cell B2 "1:25" formula bar shows "1:25:00 AM"
I enter in cell B3 "24:05" formula bar shows "1/1/1900 12:05:00 AM"
I enter in cell B4 "=SUM(B3:B4)" formula bar shows "=SUM(B3:B4)"

I highlight all cells and use custom h:mm format. Something very weird is happening when i enter cell B3's values, excel is automatically converting it to a date and time.

**excelinator** · 05-08-2010, 12:57 PM

ok I have to use [h]:mm not h:mm in order to get it to work. What is the difference between using the braces versus not?

**excelinator** · 05-08-2010, 12:58 PM

Thank you guys for your help. Much appreciated.

**daddylonglegs** · 05-08-2010, 01:05 PM

Originally Posted by excelinator

ok I have to use [h]:mm not h:mm in order to get it to work. What is the difference between using the braces versus not?

If you use h:mm you get "clock time". I.e. that will never display higher than 23:59, but [h]:mm gives you "elapsed time" so it will display any number of hours.

Note that it doesn't change the actual value, just the display