Subtracting Milliseconds based on varying Lens

[ExcelQuestFL]

Hello there excel fans,

I've been working on a time formula to concatenate numeric values, then switch to a time with milliseconds, and I'm almost there. I have that 1st part covered; however, my concatenated time values switch between len 9, 10, or 11. I think a nested if statement is what I need, but unsuccessful so far.

So far this is what I've done:

(1) Create new column, then concatenate 4 columns =CONCATENATE(A2,":",B2,":",C2,":",D2)

(2) Then create next column =TIME(MID(E2,1,1),MID(E2,3,2),MID(E2,6,2))+RIGHT(E2,3)/24/60/60/1000 with formatting of hh.mm.ss.000.

This works when len=11.

Here's the caveat

When len=10, should be =TIME(MID(E2,1,1),MID(E2,3,2),MID(E2,6,2))+RIGHT(E2,2)/24/60/60/1000 - the "right" is adjusted to 2.

When len=9, should be =TIME(MID(E2,1,1),MID(E2,3,2),MID(E2,6,2))+RIGHT(E2,1)/24/60/60/1000 - the right is adjusted to 1.

I'm trying a nested IF statement so my conversions work combining a 9 and 11 len, but that's not working so haven't even tried adding the 10 len yet. This is what I have for a 9 and 11 len,
=IF(LEN(E67=11),TIME(MID(E67,1,1),MID(E67,3,2),MID(E67,6,2))+RIGHT(E67,3)/24/60/60/1000,IF(LEN(E67=9),TIME(MID(E67,1,1),MID(E67,3,2),MID(E67,6,2))+RIGHT(E67,1)/24/60/60/1000))
which the output looks like it's just adding a 000 at the end. Concatenated value is 2:56:18:7, which should output 2:56:18:007, however, it's doing 2:56:18:000?

Can someone take a look at my sample spreadsheet please. I'm up to cell F67 and need to proceed down.

MillisecondLen_WorkSample.xlsx

Once I have that then I can do my time subtractions in Column G, and it's cake

Any assistance is much appreciated!!

And if you guys have any other recommendations to get columns A,B,C,D into a numeric value faster, I'm all ears

[Pepe Le Mokko]

In E2 enter =TIME(A2,B2,C2)+D2/(24*60*60*1000) and custom format as [h]:mm:ss.000 This will give you the time

Col F is not necessary

[ExcelQuestFL]

This forum never ceases to amaze me. THANK YOU SO MUCH!! This works better and I have the desired results without that messy nested IF formula. Enjoy your Friday and than you again!!

[Pepe Le Mokko]

You're welcome