convert a number to time

  • Thread starter Thread starter rbus
  • Start date Start date
R

rbus

Hello everybody !!

Do you any idea for my problem ?
I've a column with times : HHMMSS
145204 == 2PM 52 min 04 sec
130298
000159
How to easily format this column as time format : 14:52:04 for example ?
Thank you for your help !!
rb
 
Judging by your list below, the numbers are stored as text (otherwise the
last item would read 150).

You can't easily format the existing cells but you can extract the
information in time format to an adjacent cell.

Try this assuming your number is in cell A3:
=TIMEVALUE(LEFT(A3,2)&":"&MID(A3,3,2)&":"&RIGHT(A3,2))
Fpormat the cell as Time.

Ian
 
One way:

If you just want to display the number as a time:


Format/Cells/Number/Custom 00\:00\:00

If you actually want to convert the number to a time:

B1: =--TEXT(A1,"00\:00\:00")

To convert the number into a time in-place will require a macro.
 
Back
Top