Convert Decimal time to hh:ss in cell

[crashcb18]

All,
I'm trying to convert the decimal value of time to the military time hh:ss inside a cell without the Format window. The VBA property [numberformat] doesn't seem to work, and I guess Excel 2010 took out the "Format" function? i.e.:

H14 = 13:30 (as text format, from some other cell code)
H16 = TIMEVALUE(H14) which equals 0.5625

Now, I'd like to turn H16 into an actual number. Basically, I'm trying to turn a cell of "13:30 Z" and just extract the numbers, which I did fine, but then turn the text 13:30 into actual time number 13:30.

 

[shg]

H16 is a number; just format it as hh:mm

If A1 constains 13:30 Z, then =--LEFT(A1,5) returns 0.5624 which can be formatted as hh:mm to see 13:30

 

[crashcb18]

Thanks! My question though, is what syntax do I type into the cell to reflect hh:mm? I get the "hh:mm" part, but what function do I type in front of it? "FORMAT(CELL,"hh:ss")?

 

[shg]

You mean in VBA?

Range("A1").NumberFormat = "hh:mm"

... or from the UI?

Select the cell, do Ctrl+1, select the Number tab, select Custom in the Category box, and put hh:mm in the Type box.