Text in time with milliseconds

Question

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 29 Mar 17 at 16:19

2 answers

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 29 Mar 17 at 17:18

Scott craner · Accepted Answer · 2017-03-29T16:27:26+0000

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