correcting a negative date formula

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

[BenjieLop]

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]

You can also try this ...

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


Regards.

[Ron Rosenfeld]

On Tue, 25 Oct 2005 08:29:32 -0500, woodlot4
<[email protected]> 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

[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)


"woodlot4" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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.

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"woodlot4" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> woodlot4
> ------------------------------------------------------------------------
> woodlot4's Profile:
http://www.excelforum.com/member.php...o&userid=26613
> View this thread: http://www.excelforum.com/showthread...hreadid=479097
>