How to transfer "text" into "date"?
 

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

How to transfer "text" into "date"?
 

Select the range. From menu DataText to columnsNextNext 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
---------------
Jacob Skaria


"lalann" wrote:

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

How to transfer "text" into "date"?
 

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

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))

"lalann" wrote:

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

How to transfer "text" into "date"?
 

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.

"Jacob Skaria" wrote:

Select the range. From menu DataText to columnsNextNext 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
---------------
Jacob Skaria


"lalann" wrote:

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

How to transfer "text" into "date"?
 

Hi Eduardo,

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

"Eduardo" wrote:

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

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))

"lalann" wrote:

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

How to transfer "text" into "date"?
 

Excel 2003 does indeed have that function.
--
David Biddulph

lalann wrote:
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.

"Jacob Skaria" wrote:

Select the range. From menu DataText to columnsNextNext 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
---------------
Jacob Skaria


"lalann" wrote:

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

How to transfer "text" into "date"?
 

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

lalann wrote:
Hi Eduardo,

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

"Eduardo" wrote:

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

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))

"lalann" wrote:

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

How to transfer "text" into "date"?
 

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.:p)

Thanks all.

"David Biddulph" wrote:

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

lalann wrote:
Hi Eduardo,

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

"Eduardo" wrote:

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

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))

"lalann" wrote:

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

How to transfer "text" into "date"?
 

.... 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))
--
David Biddulph

David Biddulph wrote:
Instead of
=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2))),
Eduardo may have intended to say
=DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE( LEFT(A1,2)))
lalann wrote:
Hi Eduardo,

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

"Eduardo" wrote:

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

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))

"lalann" wrote:

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