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.

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.

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.

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.

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.

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.

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

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

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

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

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

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

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


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