Special string convertion to date and time
 

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

Special string convertion to date and time
 

Rather than fixed fields, look for that first blank:

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

Special string convertion to date and time
 

On Feb 1, 8:46*am, James Ravenswood
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

Special string convertion to date and time
 

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.

Special string convertion to date and time
 

On Wed, 1 Feb 2012 05:22:10 -0800 (PST), c stinz wrote:

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(A 1,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?

Special string convertion to date and time
 

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