On 2/02/2012 5:00 AM, c stinz wrote:
On Feb 1, 8:46 am, James
wrote:
Rather than fixed fields, look for that first blank:

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))

Thank you for your time.

I tried but it gives me VALUE error

Hi

Change Cell Format to a custom

dd/mm/yy h:mm:ss AM/PM

This will give the following result:

cell A1: 10/10/2011 2:20:00 PM
cell A2: 01/18/2011 2:20:00 AM

Unless you want to use 24 hour military time, then

dd/mm/yy hh:mm:ss

This will give the following result:

cell A1: 10/10/2011 14:20:00
cell A2: 01/18/2011 02:20:00

HTH
Mick.