J
Jeremy
What is the best way to convert numbers to time format.
Example
173100 = 5:00:00 PM
70652 =7:06:52 AM
Example
173100 = 5:00:00 PM
70652 =7:06:52 AM
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
cm
try this: assuming the number to be converted is in cell c13:
=TIME(TRUNC(C13/10000),TRUNC((C13-TRUNC(C13/10000)*10000)/100),((C13/100)-TRUNC(C13/100))*100)
=TIME(TRUNC(C13/10000),TRUNC((C13-TRUNC(C13/10000)*10000)/100),((C13/100)-TRUNC(C13/100))*100)
D
David Biddulph
You'll have to explain the logic of your system.
If 173100 had been 5:31:00 PM instead of 5:00:00 PM we would have found it
easier to understand. In that case I would have suggested
=--TEXT(A1,"00\:00\:00") and formatting the cell accordingly.
If 173100 had been 5:31:00 PM instead of 5:00:00 PM we would have found it
easier to understand. In that case I would have suggested
=--TEXT(A1,"00\:00\:00") and formatting the cell accordingly.
N
NBVC
Try:
=TIME(LEFT(TEXT(A2,REPT(0,6)),2),MID(TEXT(A2,REPT(0,6)),3,2),RIGHT(A2,2))
copied down... where A2 has first entry.
Then copy/Paste Special >> Values over original if required.
I assume first entry was supposed to result in 5:31:00 PM?
--
NBVC
Where there is a will there are many ways.
'The Code Cage' (http://www.thecodecage.com)
=TIME(LEFT(TEXT(A2,REPT(0,6)),2),MID(TEXT(A2,REPT(0,6)),3,2),RIGHT(A2,2))
copied down... where A2 has first entry.
Then copy/Paste Special >> Values over original if required.
I assume first entry was supposed to result in 5:31:00 PM?
--
NBVC
Where there is a will there are many ways.
'The Code Cage' (http://www.thecodecage.com)