Text in time with milliseconds
In column A1, I have 16304238 which represents the hour, minutes, seconds and milliseconds that I want to display in B1 as 4: 30: 42: 380 PM.
I am able to format with hours and minutes, but cannot get seconds and millisecond right. I tried
=TIME(LEFT(E2,2),MID(E2,3,2),RIGHT(E2,2))
Which comes out as 16: 30: 38.000. Also, I am using
[h]:mm:ss.000
to format the result. What should I use instead of [h] to get AM / PM or military time?
+3
Diomedes
source
to share
2 answers
Change the formula to:
=--CONCATENATE(LEFT(E2,2),":",MID(E2,3,2),":",MID(E2,5,2),".",RIGHT(E2,2))
Then format the cell:
h:mm:ss.000 AM/PM
+5
Scott craner
source
to share
C 16304238
as a true number in A1,
=--TEXT(A1*POWER(10, MIN(0, 6-LEN(A1))), "00\:00\:00.000")
This spans 0, 1, 2, or 3 decimal places. The result requires formatting the cell as h:mm:ss.000 AM/PM
.
+3
Jeeped
source
to share