Text to Time with Milliseconds

Question

In column A1 I have 16304238, which represents Hour, Minutes, Seconds and Milliseconds, which I want to display in B1 as 4:30:42:380 PM.

I know how to format with hour and minutes, but can't get the seconds and millisecond correct. I tried

=TIME(LEFT(E2,2),MID(E2,3,2),RIGHT(E2,2))

Which comes out as 16:30:38.000. Also, I'm using

[h]:mm:ss.000

to format the result. What do I need to use, instead of [h], to get AM/PM instead or military time?

Answer 1

With 16304238 as a true number in A1,

=--TEXT(A1*POWER(10, MIN(0, 6-LEN(A1))), "00\:00\:00.000")

This covers 0, 1, 2 or 3 decimal places. The result requires cell formatting as h:mm:ss.000 AM/PM.

Answer 2

Change your formula to:

=--CONCATENATE(LEFT(E2,2),":",MID(E2,3,2),":",MID(E2,5,2),".",RIGHT(E2,2))

Then format the cell to:

h:mm:ss.000 AM/PM