change text to real dates

  • Thread starter Thread starter driller
  • Start date Start date
D

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.
 
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
 
Jacob,

after selecting the dateresults the data produced is the same.
Any ideas? thanks for reply.
 
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
 
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
 
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?
 
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
 
Jacob,

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

Mike
 
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.
 
Yes Mike; an excellent source .... and also a nice way to resolve
disputes!!!.kidding)
 
Hmmm,

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

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

Mike
 
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
 
Mke & Jacob,

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


Jacob Skaria said:
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
Click to expand...
 
Back
Top