converting csv date/time fields to excel

[Chigils]

Good day everyone. My first post! I've downloaded a csv file from my companies ticketing system and need some help to make use of the data that is contained within it. I'd like to separate the date and the time into different fields. I've tried using the "left(text,[num_char]) function, but is appears that the file isn't reading the data in the existing filed as a date and a time. All suggestions welcome.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Existing field[/TD]
[TD]what I want[/TD]
[TD]what I want[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date (dd/mm/yy)[/TD]
[TD]time (24hr)[/TD]
[/TR]
[TR]
[TD]07/16/2014 09:06:00 PM[/TD]
[TD]16/07/14[/TD]
[TD]21:06[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Try

for the date
=(MID(A1,4,2)&"/"&LEFT(A1,2)&MID(A1,7,4))+0

for the time
=RIGHT(A1,11)+0
and format the cell as Custom with Type: hh:mm

 

[Chigils]

Special-K99. Like lightening! Time worked a treat, thank you. Date was not successful - I get '#VALUE'