Special converting of string date&time to date format

[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

 

[isabelle]

hi c stinz,

=DATE(MID(A1,LEN(A1)-FIND(" ",A1)-1,4),MID(A1,FIND("/",A1,FIND("/",A1)+1)-1,1),LEFT(A1,FIND("/",A1)-1))+RIGHT(A1,LEN(A1)-FIND(" ",A1))

put the cell to the desired format


--
isabelle



Le 2012-01-31 21:55, c stinz a écrit :

 

[Bruce Sinclair]

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

Finding the separators (/ - using the find function) and taking the bits
between them will work.

I suspect that, if you turn the "date" part into a number using the value
function, you get a number which you can use (yep, works for me if I type
=value("10/1/2011") - I get 40553). Format that as a date if you like.
Similarly for the time ? (not tested)

HTH

 

[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

If your strings are really in that format, and your Windows Regional Settings are the same (e.g.: MDY for the date; and ":" for the time separators), then, with the date/time text string in A1:

B1: =--A1

will convert to an Excel date/time value which can be formatted as a date & time.

If you need to separate the date and the time, then:

Date:
B1: =INT(A1)

Time:
C1: =MOD(A1,1)

Be sure to format B1 and C1 appropriately.

 

[isabelle]

correction:

y: =MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4)
m: =LEFT(A2,FIND("/",A2)-1)
d: =MID(A2,FIND("/",A2,FIND("/",A2)+1)-1,1)
h: =RIGHT(A2,LEN(A2)-FIND(" ",A2))

=DATE(y,m,d)+h


=DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1))
+RIGHT(A2,LEN(A2)-FIND(" ",A2))



--
isabelle



Le 2012-01-31 23:46, isabelle a écrit :