Convert number to time format

M

Meg

I am curious as to how to change a number 1256 into the time format of 12:56 pm or 2234 to the likes of 22:34pm. I don't need the pm or the am included, but am baffled as to how to do this. I have tried to do custom formatting, but am not really sure about the notation
 
D

Dave Peterson

I'd use a helper cell containing a formula

=TIMEVALUE(TEXT(A1,"00\:00"))

And formatted as time (hh:mm)

(copy|pastespecial|values if you want to get rid of the original data.)
 
J

JE McGimpsey

One way:

If the number is in cell A1:


=TIME(INT(A1/100),MOD(A1,100),0)

Since XL stores times as fractional days, changing the formatting won't
actually change the value to a time.

If you don't want to convert the number, but only want to display it in
time format, use

Format/Cells/Number/Custom 00":"00
 
Top