Convert number to time format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.)
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

convert time format 2
Convert time from text 2
Convert Number to Time 6
automatic time formatting 3
Creating uniform time format from multiple formats 1
"h:mm" format, but 12-hour time, not 24-hour time? 4
Convert Text numbers to time format 3
Time formatting in Excel 2003 2

Back
Top