If a date has been entered 20051103, is there a way to convert it to
11/03/2005?

T

Presuming data in column A, use a new empty column and enter

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

and format the resulting number as a sate, via FormatCells...Number

You can then copy these new dates and Paste Special...Values and number
formats over the old ones

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"T" wrote in message
...
If a date has been entered 20051103, is there a way to convert it to
11/03/2005?

On Thu, 3 Nov 2005 14:26:12 -0800, "T" wrote:

If a date has been entered 20051103, is there a way to convert it to
11/03/2005?

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

or

=--TEXT(A1,"0000\/00\/00")

The first will convert "illegal" dates, such as 20050132 -- 1 Feb 2005

The second will give an error for "illegal" dates.
--ron

Select the column containing the dates, then:
<Data <TextToColumns <Next <Next,
Click on "Date", and expand the date window,
And choose "YMD",
Then <Finish

This will give you "legal" XL dates,
Which you can the format any way you wish, if they didn't happen to come up
with the exact display that you wanted.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"T" wrote in message
...
If a date has been entered 20051103, is there a way to convert it to
11/03/2005?