I think you need to format the cell from text to date


"Bobbie" wrote in message
...

Without any explanation in the body of the post I will assume form the
leading zero that the cell is formatted as text. If that is so then try the
formula:

=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,MID(E9,3,2),LEFT(E9,2))

This is for British style dates. For American dates try:

=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,LEFT(E9,2),MID(E9,3,2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Bobbie" wrote in message
...

This seemed to work okay (U.S format). Change the cell format to date.
=--(LEFT(E9,2)&"/"&MID(E9,3,2)&"/"&RIGHT(E9,2))



"Sandy Mann" wrote:

Without any explanation in the body of the post I will assume form the
leading zero that the cell is formatted as text. If that is so then try the
formula:

=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,MID(E9,3,2),LEFT(E9,2))

This is for British style dates. For American dates try:

=DATE(1900+RIGHT(E9,2)+(--RIGHT(E9,2)<=30)*100,LEFT(E9,2),MID(E9,3,2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Bobbie" wrote in message
...

DataText to ColumnsNextNextColumn Data FormatDateDMY and Finish.


Gord Dibben MS Excel MVP

On Fri, 3 Nov 2006 12:07:02 -0800, Bobbie
wrote: