Extract time from DateTime format

[ascalise]

Having trouble isolating the time component from some .csv data I'm importing. The datetime comes in as dd-mmm-yyyy hh:mm:ss:000 format, I ran a replace to repair the colon separator between seconds and milliseconds. Now I just want the hh:mm:ss.000 component. I tried to simply reformat with a custom hh:mm:ss.000, but it won't change correctly unless I press manually enter in every cell which isn't feasible for some of my larger datasets.

I have an example workbook I can share, but I can't download XL2BB to this system. Here's a screenshot of some of the data:

 

[JimM]

Does this work ?

=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

 

[Joe4]

Another option:

=B2-INT(B2)

and format cell in "hh:mm:ss" format.

 

[Fluff]

Another option, which will do it wihtout the helper column

=MOD(SUBSTITUTE(A2,":",".",3)+0,1)

 

[ascalise]

Does this work ?

=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

It converts to time but truncates the milliseconds. Returns .000 after the seconds value.

Another option:

=B2-INT(B2)

and format cell in "hh:mm:ss" format.

Worked like a charm, thanks.

 

[Joe4]

You are welcome.
Glad we could help.