correcting a negative date formula

W

woodlot4

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.
 
B

BenjieLop

woodlot4 said:
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.
Click to expand...


Try this ...

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


Regards.
 
R

Ron Rosenfeld

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.
Click to expand...


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

and format it as mm/dd/yyyy


--ron
 
B

Bob Phillips

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

or use Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish

--

HTH

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

woodlot4

This one worked for me:
-or use Data>Text to Columns>Next>Next>Column Data
Format>Date>YMD>Finish
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.
 
R

Ragdyer

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.
 
Top