Changing date/time format

[njmike]

All,

I have been racking my brain out on this for several hours and have no answer, but know there must be an easier way to do this.

I receive a CSV file and there are 3 columns labeled Call date, PartStarttime, & PartEndTime. Each of these columns is formated as general (because it is coming from a CSV file) and they represent the date and time (DD/MM/YYYY hh:mm:ss AM/PM).

I need to reformat each of these so that Call Date is in the U.S. format of MM/DD/YYYY hh:mm:ss 24hr and the other 2 are simply just the time in 24 hour.

I have attached it here and would appreciate any help.

I have tried the Data-->Texts to Column, but that did not work.

thanks
Mike

[Jonmo1]

That came through as a mixture of dates and text strings..
Most show as just text strings, but a few (A13 and A14 for example) show up as real dates..

But to get a date and time (seperately) from a Text string like
28/01/2015 01:54:10 pm

Try
Date: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))

Time: =TIMEVALUE(RIGHT(A2,11))

Format the cells with the formulas appropriately as Date and Time.

[njmike]

Thanks. Exactly what I needed.

[Jonmo1]

You're welcome.