change text to real dates
 

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Select the range of dates which needs to be corrected. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will
take you to Step 3 of 3 of the Wizard. From Column Data format select Date
and select the date format in which your data is (DMY).Hit Finish. MSExcel
will now convert the dates to the default date format of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Jacob,

after selecting the dateresults the data produced is the same.
Any ideas? thanks for reply.

"Jacob Skaria" wrote:

Select the range of dates which needs to be corrected. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will
take you to Step 3 of 3 of the Wizard. From Column Data format select Date
and select the date format in which your data is (DMY).Hit Finish. MSExcel
will now convert the dates to the default date format of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

thanks,

i got #value!.


"Mike H" wrote:

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Hi,

I suspect spaces at the start or end, try this

=DATEVALUE(LEFT(TRIM(A1),2)&"/"&MID(TRIM(A1),4,2)&"/"&RIGHT(TRIM(A1),2))

Mike

"driller" wrote:

thanks,

i got #value!.


"Mike H" wrote:

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Please check whether there are any other characters other than numerics and
dots in the cell..Just check the length of the entry using =LEN(A1). It
should be 8. If you have extra spaces use Find/Replace to replace space with
blank..and then try again..

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Jacob,

after selecting the dateresults the data produced is the same.
Any ideas? thanks for reply.

"Jacob Skaria" wrote:

Select the range of dates which needs to be corrected. From menu DataText to
Columns will populate the 'Convert Text to Columns Wizard' Hit NextNext will
take you to Step 3 of 3 of the Wizard. From Column Data format select Date
and select the date format in which your data is (DMY).Hit Finish. MSExcel
will now convert the dates to the default date format of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

using len() i got all with 8.

from the two formulas suggested, i got same results.

26.04.09----#value!
11.06.09----11/6/2009-----06-Nov-09 ??

do i have some difficulty now?

"Mike H" wrote:

Hi,

I suspect spaces at the start or end, try this

=DATEVALUE(LEFT(TRIM(A1),2)&"/"&MID(TRIM(A1),4,2)&"/"&RIGHT(TRIM(A1),2))

Mike

"driller" wrote:

thanks,

i got #value!.


"Mike H" wrote:

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Jacob,

I didn't know about this series of clips. An excellent source for beginners,
nice one.

Mike

"Jacob Skaria" wrote:

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

yeh, but not so accessible in my case.

"Mike H" wrote:

Jacob,

I didn't know about this series of clips. An excellent source for beginners,
nice one.

Mike

"Jacob Skaria" wrote:

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Yes Mike; an excellent source .... and also a nice way to resolve
disputes!!!.kidding)

"Mike H" wrote:

Jacob,

I didn't know about this series of clips. An excellent source for beginners,
nice one.

Mike

"Jacob Skaria" wrote:

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

=LEFT(A1,2)-----26
=MID(A1,4,2)-----04
=RIGHT(A1,2)-----09

len() = 8, kinda confusing...


"Mike H" wrote:

Hmmm,

Break the formula into 3 parts and tell me what each returns

=LEFT(A1,2)
=MID(A1,4,2)
=RIGHT(A1,2)

Mike

"driller" wrote:

using len() i got all with 8.

from the two formulas suggested, i got same results.

26.04.09----#value!
11.06.09----11/6/2009-----06-Nov-09 ??

do i have some difficulty now?

"Mike H" wrote:

Hi,

I suspect spaces at the start or end, try this

=DATEVALUE(LEFT(TRIM(A1),2)&"/"&MID(TRIM(A1),4,2)&"/"&RIGHT(TRIM(A1),2))

Mike

"driller" wrote:

thanks,

i got #value!.


"Mike H" wrote:

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Hmmm,

Break the formula into 3 parts and tell me what each returns

=LEFT(A1,2)
=MID(A1,4,2)
=RIGHT(A1,2)

Mike

"driller" wrote:

using len() i got all with 8.

from the two formulas suggested, i got same results.

26.04.09----#value!
11.06.09----11/6/2009-----06-Nov-09 ??

do i have some difficulty now?

"Mike H" wrote:

Hi,

I suspect spaces at the start or end, try this

=DATEVALUE(LEFT(TRIM(A1),2)&"/"&MID(TRIM(A1),4,2)&"/"&RIGHT(TRIM(A1),2))

Mike

"driller" wrote:

thanks,

i got #value!.


"Mike H" wrote:

Hi,

Try this

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))

Mike

"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

OK Try if the century is 20...

=DATE("20" & RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

yeh, but not so accessible in my case.

"Mike H" wrote:

Jacob,

I didn't know about this series of clips. An excellent source for beginners,
nice one.

Mike

"Jacob Skaria" wrote:

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards

change text to real dates
 

Mke & Jacob,

thanks..it solves ...!!! :)


"Jacob Skaria" wrote:

OK Try if the century is 20...

=DATE("20" & RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

yeh, but not so accessible in my case.

"Mike H" wrote:

Jacob,

I didn't know about this series of clips. An excellent source for beginners,
nice one.

Mike

"Jacob Skaria" wrote:

Watch this 2 min video which will help
http://www.youtube.com/watch?v=24Z8TsyRVsY

If this post helps click Yes
---------------
Jacob Skaria


"driller" wrote:

Hello,

i have extracted text within texts in a column of data thru left & right
function.

then i have the texts result like this

26.04.09
11.06.09
23.06.09
05.07.09
12.07.09
30.09.09

I need to change it to real dates considering that the first 2 digits are
Days and the last 2 digits are Years.
e.g.
26.04.09 ---- 26-Apr-09 or 26/04/09

thanks for helping. Any suggestion is appreciated.

--
regards