How to transfer "text" into "date"?

[lalann]

Hi,

The dates shown in the report are in text format. As you can see below, it
is "DD-MM-YYYY". But as it's is in text format, I can't use it as "date" to
do the further analysis. Is there any ways to change the below text into
"date"? Thanks.

14-12-1969
26-12-1962
01-06-1960
29-05-1963

 

[Jacob Skaria]

Select the range. From menu Data>Text to columns>Next>Next will take you to
Step 3 of Wizard. Select Date and select the format (DMY) and hit finish.
Excel will convert this to date formats. Try and feedback

If this post helps click Yes

 

[Eduardo]

Hi,
I assumed your information is in column A, in column B enter

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(RIGHT(A1,2)))

 

[lalann]

Hi Jacob,
I am using Excel 2003. So I don't have this funtion. May I ask if there is
any other way to do it? Thanks.

 

[lalann]

Hi Eduardo,

No, it doesn't work. It showed "#VALUE!".

 

[David Biddulph]

Excel 2003 does indeed have that function.

 

[David Biddulph]

Instead of =DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(RIGHT(A1,2))),
Eduardo may have intended to say
=DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE(LEFT(A1,2)))

 

[lalann]

Hi David,

Thanks. It works. I also find Text to columns in menu. (My excel is not in
English, so try to find what "Text to columns" is.)

Thanks all.

 

[David Biddulph]

.... and in fact Excel doesn't need the VALUE function in this case, so you
can simplify to
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))