Converting Text Values to Dates
 

Is there a way to convert a column of text, date values (entered as 81096,
for example) to valid date format, 8/10/96? I could not get the DATEVALUE
function to do this.

Converting Text Values to Dates
 

Hi!

Select the range of cells in question.

Goto DataText to Columns
Click NEXT twice
Select Column Data Format: DATE. Then choose the format from the drop down
Finish

Biff

"Frank Winston" wrote in message
...
Is there a way to convert a column of text, date values (entered as 81096,
for example) to valid date format, 8/10/96? I could not get the DATEVALUE
function to do this.

Converting Text Values to Dates
 

In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered as 81096,
for example) to valid date format, 8/10/96? I could not get the DATEVALUE
function to do this.

Converting Text Values to Dates
 

Try that with this:

120100

Biff

"Gary''s Student" wrote in message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered as
81096,
for example) to valid date format, 8/10/96? I could not get the
DATEVALUE
function to do this.

Converting Text Values to Dates
 

What I got was:

120100 12 1 0 12/1/1900

I see your point. My method will always map into the last century.
Good catch!
--
Gary's Student


"Biff" wrote:

Try that with this:

120100

Biff

"Gary''s Student" wrote in message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered as
81096,
for example) to valid date format, 8/10/96? I could not get the
DATEVALUE
function to do this.

Converting Text Values to Dates
 

TTC is usually the easiest and the best way to convert this type of text to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be made (not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom formatted to
the desired display.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Gary''s Student" wrote in message
...
What I got was:

120100 12 1 0 12/1/1900

I see your point. My method will always map into the last century.
Good catch!
--
Gary's Student


"Biff" wrote:

Try that with this:

120100

Biff

"Gary''s Student" wrote in
message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered as
81096,
for example) to valid date format, 8/10/96? I could not get the
DATEVALUE
function to do this.

Converting Text Values to Dates
 

Whilst I agree that TTC is usually best, it will correctly convert 6
character dates, it seems to have no luck with 5 character dates as
displayed by the OP.

Providing that the middle unit (Months for English dates, Days for
American dates)are entered as 2 digit format (the number '10' is not a
good indicater) then either a formula of

=text(a1,"000000")

and then put through TTC, or


=IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2),LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&MID(A1,4,2))
or

=IF(LEN(A1)=6,MID(A1,3,2)&"/"&MID(A1,1,2)&"/"&MID(A1,5,2),MID(A1,2,2)&"/"&MID(A1,1,1)&"/"&MID(A1,4,2))

will give an English or American date format.

However, if the middle figure (of DMY or MDY) is a single digit, there
is no way to detect 11196 as being a mid-January or early-November date
in either system.

Hope this helps (as opposed to adding confusion)


RagDyer Wrote:
TTC is usually the easiest and the best way to convert this type of text
to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be made
(not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom formatted
to
the desired display.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"Gary''s Student" wrote in
message
...
What I got was:

120100 12 1 0 12/1/1900

I see your point. My method will always map into the last century.
Good catch!
--
Gary's Student


"Biff" wrote:

Try that with this:

120100

Biff

"Gary''s Student" wrote
in
message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered
as
81096,
for example) to valid date format, 8/10/96? I could not get
the
DATEVALUE
function to do this.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=483305

Converting Text Values to Dates
 

no way to detect 11196 as being a mid-January or early-November

There's always a monkey wrench!

Biff

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1y7rva_1131513601.0954@excelforu m-nospam.com...

Whilst I agree that TTC is usually best, it will correctly convert 6
character dates, it seems to have no luck with 5 character dates as
displayed by the OP.

Providing that the middle unit (Months for English dates, Days for
American dates)are entered as 2 digit format (the number '10' is not a
good indicater) then either a formula of

=text(a1,"000000")

and then put through TTC, or


=IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2),LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&MID(A1,4,2))
or

=IF(LEN(A1)=6,MID(A1,3,2)&"/"&MID(A1,1,2)&"/"&MID(A1,5,2),MID(A1,2,2)&"/"&MID(A1,1,1)&"/"&MID(A1,4,2))

will give an English or American date format.

However, if the middle figure (of DMY or MDY) is a single digit, there
is no way to detect 11196 as being a mid-January or early-November date
in either system.

Hope this helps (as opposed to adding confusion)


RagDyer Wrote:
TTC is usually the easiest and the best way to convert this type of text
to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be made
(not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom formatted
to
the desired display.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"Gary''s Student" wrote in
message
...
What I got was:

120100 12 1 0 12/1/1900

I see your point. My method will always map into the last century.
Good catch!
--
Gary's Student


"Biff" wrote:

Try that with this:

120100

Biff

"Gary''s Student" wrote
in
message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered
as
81096,
for example) to valid date format, 8/10/96? I could not get
the
DATEVALUE
function to do this.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=483305

Converting Text Values to Dates
 

I agree with you that 6 digits will always work correctly, but ... allow me
to nit-pick.<g

The 5 digits as posted by the OP as an American format (mdy) will also
*always* work correctly.

The actual criteria of accuracy being that the middle reference *and* the
ending reference are *2* digits.

Try *any* American 5 digit format with the above criteria and you'll see
what I mean.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1y7rva_1131513601.0954@excelforu m-nospam.com...

Whilst I agree that TTC is usually best, it will correctly convert 6
character dates, it seems to have no luck with 5 character dates as
displayed by the OP.

Providing that the middle unit (Months for English dates, Days for
American dates)are entered as 2 digit format (the number '10' is not a
good indicater) then either a formula of

=text(a1,"000000")

and then put through TTC, or


=IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2),LEFT(A1,1)&"/"&MID(
A1,2,2)&"/"&MID(A1,4,2))
or

=IF(LEN(A1)=6,MID(A1,3,2)&"/"&MID(A1,1,2)&"/"&MID(A1,5,2),MID(A1,2,2)&"/"&MI
D(A1,1,1)&"/"&MID(A1,4,2))

will give an English or American date format.

However, if the middle figure (of DMY or MDY) is a single digit, there
is no way to detect 11196 as being a mid-January or early-November date
in either system.

Hope this helps (as opposed to adding confusion)


RagDyer Wrote:
TTC is usually the easiest and the best way to convert this type of text
to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be made
(not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom formatted
to
the desired display.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
--------------------------------------------------------------------------
-

"Gary''s Student" wrote in
message
...
What I got was:

120100 12 1 0 12/1/1900

I see your point. My method will always map into the last century.
Good catch!
--
Gary's Student


"Biff" wrote:

Try that with this:

120100

Biff

"Gary''s Student" wrote
in
message
...
In A1: 81096
In B1: =INT(A1/10000) month part shows 8
In C1: =INT((A1-10000*B1)/100) day part shows 10
In D1: =A1-10000*B1-100*C1 year part shows 96
In E1: =DATE(D1,B1,C1)
--
Gary's Student


"Frank Winston" wrote:

Is there a way to convert a column of text, date values (entered
as
81096,
for example) to valid date format, 8/10/96? I could not get
the
DATEVALUE
function to do this.





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=483305

Converting Text Values to Dates
 

The date posted by the OP was either the 10th day of August or the 8th
day of October, or perhaps the 6th of September 1981, and I see nothing
in the OP's posting to indicate which, but I am unaware of the OP's
details except those included in the post

As I commented earlier, I see no date function that can determine
whether 11196 is the 1st of November or the 11th of January, but I
agree that the year appears to be 1996.

As I also said, 10 is not a fair indicator of whether all dates have
two digits for the middle portion.


RagDyeR Wrote:[color=blue]
I agree with you that 6 digits will always work correctly, but ... allow
me
to nit-pick.<g

The 5 digits as posted by the OP as an American format (mdy) will also
*always* work correctly.

The actual criteria of accuracy being that the middle reference *and*
the
ending reference are *2* digits.

Try *any* American 5 digit format with the above criteria and you'll
see
what I mean.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1y7rva_1131513601.0954@excelforu m-nospam.com...

Whilst I agree that TTC is usually best, it will correctly convert 6
character dates, it seems to have no luck with 5 character dates as
displayed by the OP.

Providing that the middle unit (Months for English dates, Days for
American dates)are entered as 2 digit format (the number '10' is not a
good indicater) then either a formula of

=text(a1,"000000")

and then put through TTC, or


=IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2),LEFT(A1,1)&"/"&MID(
A1,2,2)&"/"&MID(A1,4,2))
or

=IF(LEN(A1)=6,MID(A1,3,2)&"/"&MID(A1,1,2)&"/"&MID(A1,5,2),MID(A1,2,2)&"/"&MI
D(A1,1,1)&"/"&MID(A1,4,2))

will give an English or American date format.

However, if the middle figure (of DMY or MDY) is a single digit, there
is no way to detect 11196 as being a mid-January or early-November
date
in either system.

Hope this helps (as opposed to adding confusion)


RagDyer Wrote:
TTC is usually the easiest and the best way to convert this type of
text
to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be
made
(not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom
formatted
to
the desired display.
--
Regards,

RD


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=483305

Converting Text Values to Dates
 

On Tue, 8 Nov 2005 14:07:02 -0800, "Frank Winston"
wrote:

Is there a way to convert a column of text, date values (entered as 81096,
for example) to valid date format, 8/10/96? I could not get the DATEVALUE
function to do this.

It depends on what that date value represents.

If it represents 10 Aug 1996 then:

=DATE(MOD(A1,100)+1900+100*(MOD(A1,100)<30),INT(A1/10^4),MOD(INT(A1/100),100))

If it represents 8 Oct 1996 then:

=DATE(MOD(A1,100)+1900+100*(MOD(A1,100)<30),MOD(IN T(A1/100),100),INT(A1/10^4))

You could also try:

=--TEXT(A1,"00\/00\/00")

or

=DATEVALUE(TEXT(A1,"00\/00\/00"))

and see if the output is congruent with your regional settings.


--ron

Converting Text Values to Dates
 

Don't wish to start anything confrontational Bryan, but this is quoted from
the OP:

<<<"(entered as 81096, for example) to valid date format, 8/10/96"

So, you must admit, it's *not* as you said:
<<<"or perhaps the 6th of September 1981, and I see nothing in the OP's
posting to indicate which"

AND ... here is what I said (emphasis added):
<<<"The 5 digits as posted by the OP ***[ as an American format (mdy) ]***
will also
*always* work correctly.
The actual criteria of accuracy being that the middle reference *and* the
ending reference are *2* digits."

The intentions of the OP are really a mute point.

The point in focus was your statement that:
<<<"it (TTC) seems to have no luck with 5 character dates as displayed by
the OP"

I just wished to show that there are circumstances where 5 characters would
definitely *always* produce accurate returns, and I mentioned those
circumstances.

So, to *rephrase* my post:

TTC will *always* return correct dates in American format using 5 digits as
long as the middle and ending references are 2 digits.

That's all I said.
It shouldn't be confusing and should be easily understood.

11196 is *not* ambiguous using the criteria I stated above.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1y9x6m_1131613807.0867@excelforu m-nospam.com...

The date posted by the OP was either the 10th day of August or the 8th
day of October, or perhaps the 6th of September 1981, and I see nothing
in the OP's posting to indicate which, but I am unaware of the OP's
details except those included in the post

As I commented earlier, I see no date function that can determine
whether 11196 is the 1st of November or the 11th of January, but I
agree that the year appears to be 1996.

As I also said, 10 is not a fair indicator of whether all dates have
two digits for the middle portion.


RagDyeR Wrote:
I agree with you that 6 digits will always work correctly, but ... allow
me
to nit-pick.<g

The 5 digits as posted by the OP as an American format (mdy) will also
*always* work correctly.

The actual criteria of accuracy being that the middle reference *and*
the
ending reference are *2* digits.

Try *any* American 5 digit format with the above criteria and you'll
see
what I mean.

--

Regards,

RD
--------------------------------------------------------------------------
--
-------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------------
--
-------------------

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1y7rva_1131513601.0954@excelforu m-nospam.com...

Whilst I agree that TTC is usually best, it will correctly convert 6
character dates, it seems to have no luck with 5 character dates as
displayed by the OP.

Providing that the middle unit (Months for English dates, Days for
American dates)are entered as 2 digit format (the number '10' is not a
good indicater) then either a formula of

=text(a1,"000000")

and then put through TTC, or



=IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2),LEFT(A1,1)&"/"&MID(
A1,2,2)&"/"&MID(A1,4,2))
or


=IF(LEN(A1)=6,MID(A1,3,2)&"/"&MID(A1,1,2)&"/"&MID(A1,5,2),MID(A1,2,2)&"/"&MI[color=blue]
D(A1,1,1)&"/"&MID(A1,4,2))

will give an English or American date format.

However, if the middle figure (of DMY or MDY) is a single digit, there
is no way to detect 11196 as being a mid-January or early-November
date
in either system.

Hope this helps (as opposed to adding confusion)


RagDyer Wrote:
TTC is usually the easiest and the best way to convert this type of
text
to
true dates,
*BUT*
In the third page of the wizard, after clicking on "Date",
One must realize that the format to click on,
Is *not* the format you want to display,
BUT the format that the present text is currently in.
You're telling TTC where to convert *from*.

If this choice is not done correctly, either no conversion will be
made
(not
so bad), or a wrong conversion will be made (bad).

After the true dates are established, then they can be custom
formatted
to
the desired display.
--
Regards,

RD


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=483305

Converting Text Values to Dates
 

Very Big thanks to Mr. Biff for the solution you provide for transfering
text to date. i was struggling for many days :) :) :cool:


--
yousif
------------------------------------------------------------------------
yousif's Profile: http://www.excelforum.com/member.php...o&userid=35093
View this thread: http://www.excelforum.com/showthread...hreadid=483305