I have a sheet of data that has dates listed in reverse order as a
number. I cannot get excel to read it as a date and put it in the
correct date format. It currently reads like this: 20050921
The first 4 numbers are the year and the next two are the month and
then the day. I want it to read mm/dd/yyyy. Does anyone know how I
can do this with a formula? I do no know how to create a macro but I
can figure out most formulas. I just have not been able to come up
with anything that will correct this for me.


--
woodlot4
------------------------------------------------------------------------
woodlot4's Profile: http://www.excelforum.com/member.php...o&userid=26613
View this thread: http://www.excelforum.com/showthread...hreadid=479097

woodlot4 Wrote:
I have a sheet of data that has dates listed in reverse order as a
number. I cannot get excel to read it as a date and put it in the
correct date format. It currently reads like this: 20050921
The first 4 numbers are the year and the next two are the month and
then the day. I want it to read mm/dd/yyyy. Does anyone know how I
can do this with a formula? I do no know how to create a macro but I
can figure out most formulas. I just have not been able to come up
with anything that will correct this for me.


Try this ...

=MID(A1,5,2)&\"/\"&RIGHT(A1,2)&\"/\"&LEFT(A1,4)


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=479097

You can also try this ...

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


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=479097

On Tue, 25 Oct 2005 08:29:32 -0500, woodlot4
wrote:


I have a sheet of data that has dates listed in reverse order as a
number. I cannot get excel to read it as a date and put it in the
correct date format. It currently reads like this: 20050921
The first 4 numbers are the year and the next two are the month and
then the day. I want it to read mm/dd/yyyy. Does anyone know how I
can do this with a formula? I do no know how to create a macro but I
can figure out most formulas. I just have not been able to come up
with anything that will correct this for me.


=DATE(INT(A1/10^4),MOD(INT(A1/100),100),MOD(A1,100))

and format it as mm/dd/yyyy


--ron

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

or use DataText to ColumnsNextNextColumn Data FormatDateYMDFinish

--

HTH

RP
(remove nothere from the email address if mailing direct)


"woodlot4" wrote in
message ...

I have a sheet of data that has dates listed in reverse order as a
number. I cannot get excel to read it as a date and put it in the
correct date format. It currently reads like this: 20050921
The first 4 numbers are the year and the next two are the month and
then the day. I want it to read mm/dd/yyyy. Does anyone know how I
can do this with a formula? I do no know how to create a macro but I
can figure out most formulas. I just have not been able to come up
with anything that will correct this for me.


--
woodlot4
------------------------------------------------------------------------
woodlot4's Profile:
http://www.excelforum.com/member.php...o&userid=26613
View this thread: http://www.excelforum.com/showthread...hreadid=479097

This one worked for me:
-or use DataText to ColumnsNextNextColumn Data
FormatDateYMDFinish
I seperated everything into different columns and then cut the year and
pasted it in the order I wanted. Then I did a concatenate with a slash.
it looks like a date but does not let me format it to read like one
when I sort the info but I am many steps ahead of where I was. Thanks.


--
woodlot4
------------------------------------------------------------------------
woodlot4's Profile: http://www.excelforum.com/member.php...o&userid=26613
View this thread: http://www.excelforum.com/showthread...hreadid=479097

When you completed the TTC conversion that Bob suggested, your dates were
*true* XL recognized dates.

The changes you made to these true dates converted them back to Text again,
and therefore not recognized by XL as numbers (dates).

All you had to do was custom format that converted column to whatever
display you wish.

While the column is *still* selected from the TTC conversion, click on the
menu bar:
<Format <Cells <Number tab,
Click "Custom" in the left window,
And enter this in the "Type" box:

mm/dd/yyyy

As you enter this, right above, you'll see your desired format displayed in
the "Sample" window.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"woodlot4" wrote in
message ...

This one worked for me:
-or use DataText to ColumnsNextNextColumn Data
FormatDateYMDFinish
I seperated everything into different columns and then cut the year and
pasted it in the order I wanted. Then I did a concatenate with a slash.
it looks like a date but does not let me format it to read like one
when I sort the info but I am many steps ahead of where I was. Thanks.


--
woodlot4
------------------------------------------------------------------------
woodlot4's Profile:
http://www.excelforum.com/member.php...o&userid=26613
View this thread: http://www.excelforum.com/showthread...hreadid=479097