Special string convertion to date and time

[c stinz]

Hi there,

Working with a file and all my dates and times appear as one long text
string shown below that I would like to convert to date and time
format so that I can manipulate the data anyway I like. Data appears
as follows:
..
cell A1: 10/1/2011 2:20:00 PM
cell A2: 1/18/2011 2:20:00 AM
I have tried many different ways and sometimes it works. The problem
is when the date and month are not 2 digits. As you see the date or
the month can be 1 or 2 digits. How do I convert it so that it works
no matter what..

this works only when the month and date are both 2 digits
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A1,10)

Hope someone can help . Thanks

 

[James Ravenswood]

Rather than fixed fields, look for that first blank:

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

 

[c stinz]

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

 

[Vacuum Sealed]

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.

 

[Ron Rosenfeld]

Hi there,

Working with a file and all my dates and times appear as one long text
string shown below that I would like to convert to date and time
format so that I can manipulate the data anyway I like. Data appears
as follows:
.
cell A1: 10/1/2011 2:20:00 PM
cell A2: 1/18/2011 2:20:00 AM
I have tried many different ways and sometimes it works. The problem
is when the date and month are not 2 digits. As you see the date or
the month can be 1 or 2 digits. How do I convert it so that it works
no matter what..

this works only when the month and date are both 2 digits
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A1,10)

Hope someone can help . Thanks

You have also posted this question in microsoft.public.excel where you have several answers. What was the problem with those responses?

 

[James Ravenswood]

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