Date Time from text format to Date format

[cbx1175]

Not as easy as my google searches are showing me. I've been trying various =date, =datevalue, Text to columns to convert a text-based date/time originally saved in a CSV.

I'm trying to separate the time from the date so I can pick out blocks of time. So what I do either doesn't work or messes up with the AM/PM. I think I've started to look too much into it.

Link to file on my Google Drive so the bad formatting keeps - https://drive.google.com/open?id=1_fzDVWZINZSaq5v_4ZvoC2xzNKnePmMZ

[TABLE="width: 500"]
<tbody>[TR]
[TD]Original[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]11/20/2018 4:34:50 PM[/TD]
[TD][/TD]
[TD]11/20/2018[/TD]
[TD]16:34:50[/TD]
[/TR]
</tbody>[/TABLE]

I have a list of just over 8800 lines to do.

Thank You

 

[gaz_chops]

Hi, welcome to Mr Excel,

If you just want to extract the time, try

[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl63, width: 171, align: right"]20/11/2018 16:34[/TD]
[TD="width: 87"][/TD]
[TD="class: xl64, width: 92"]=MOD(A1,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 350"]
<colgroup><col width="171" style="width: 128pt;"><col width="87" style="width: 65pt;"><col width="92" style="width: 69pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 171, align: right"]20/11/2018 16:34[/TD]
[TD="width: 87"][/TD]
[TD="class: xl64, width: 92, align: right"]16:34:50[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]

Format the cell with the formula as h:mm:ss