change text to real dates

[driller]

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.

 

[Jacob Skaria]

Select the range of dates which needs to be corrected. From menu Data>Text to
Columns will populate the 'Convert Text to Columns Wizard' Hit Next>Next 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

 

[Mike H]

Hi,

Try this

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

Mike

 

[driller]

Jacob,

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

 

[driller]

thanks,

i got #value!.

Hi,

Try this

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

Mike

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[driller]

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?

 

[Jacob Skaria]

Watch this 2 min video which will help

If this post helps click Yes

 

[YESHWANT]

Hi driller,

select all the data you want to convert to dates,
click on edit - replace -
replace : .
with : /
replace all

after this, without deselecting the data, click on format - cell - custom -
"DD/MM/YY"

and your problem is solved

 

[Mike H]

Jacob,

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

Mike

 

[driller]

kinda confused

here is what i have along Row 1 {text formula}
A B C D
0001 - 26.04.09.xls 26.04.09 {=LEFT(RIGHT(A1,12),8)} 8 {=LEN(B1)} 26-Apr-09

Basically, i need the data at Col. B to be transferred to col. D as real
dates '26-Apr-09'. The left & Right function on Col. D may serve with a
Datevalue...I just dont have the right formula.

thanks.

Its a long list.

 

[driller]

yeh, but not so accessible in my case.

Jacob,

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

Mike

 

[Jacob Skaria]

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

 

[driller]

=LEFT(A1,2)----->26

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

len() = 8, kinda confusing...

 

[Mike H]

Hmmm,

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

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

Mike

 

[Jacob Skaria]

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

 

[driller]

Mke & Jacob,

thanks..it solves ...!!!

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