Covert date/time to military time hour only

**toontown** · 07-03-2013, 12:55 PM

Hello,

i converted "6/1/2013 1:30:41 AM" to show as just "01" (military hour), but I am not able to do anything with it because when you click on the cell it still reads as "6/1/2013 1:30:41 AM". How can i convert the date/time to just "01" so i can sort or subtotal?

**Jonmo1** · 07-03-2013, 12:57 PM

Try

=TEXT(A1,"hh")

**shg** · 07-03-2013, 01:03 PM

Or =hour(a1)

**FDibbins** · 07-03-2013, 01:04 PM

Hi and welcome to the forum

Formatting does not change the actual contents of a cell, just the cosmetics - how it looks. If you want to change the actual contents of a cell, you usually need to apply a formula to it. Something like this, in B1 (assuming your data is in A1)...

=TIME(HOUR(A1),0,0)
and then format custom as hh

**Richard Buttrey** · 07-03-2013, 01:05 PM

Hi,

What do you mean by 'converted'? Is the cell a date number like 41280 that's formatted to show just the hour. i.e. are you just formatting it rather than converting it as you say.

Maybe in another cell...
=(A1-INT(A1))*24