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

The TEXT function returns a *text* representation of the referenced argument
in the format that you specify. The referenced argument in this case are
string of digits in cell A1 that represent a date in yyyy/mm/dd format.

So, we need to tell the TEXT function to convert that string of digits to
this date format yyyy/mm/dd. That's what all the 0s do. 0000/00/00. The \
slash is a delimiter that tells the function to separate the 0s into the
groups of 0000 00 00.

The result of the TEXT function is the *text value* "2009/06/14" which is
not a true Excel date even though it looks like one. Dates in Excel are
really just numbers formatted to look like dates. For example, if you enter
the current date in a cell, 7/9/2009, Excel automatically formats the cell
as Date and it looks like a date. However, the true underlying value of that
date is really the number 40003. To see this format that date cell as
General.

Now, since the result of the TEXT function is a text value we need to
convert that into a numeric number so that Excel will recognize it as a true
Excel date. One way to do that is to use the double unary "--". It will
convert the text string "2009/06/14" to the numeric value 40003 then you
apply the date format of your choice and end up with a true Excel date.


--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
Thank you so much - This woked great! Would you be so kind as to explain
the
formula in laymen's terms? What exactly are the 2 dashes used for after
the
equal sign?
Thank you again, Karen

"T. Valko" wrote:

I don't know why it would split it into 3 cells since no delimiters were
selected.

Here's a formula method:

A1 = 20090614

B1 formula:

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

Format as Date

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
Thank you for your help. Actually I tried that before I posted this
message
and it split it into 3 columns and I want it all in one column
formatted
06/14/09.
Any other suggestions?
Karen

"T. Valko" wrote:

Sometimes this will work.

Select the range of cells you want to convert
Goto the menu DataText to Columns
Click Next twice
In Step 3 of the wizard, under Column data format, select Date and YMD
Click Finish then format in the style of your choice

--
Biff
Microsoft Excel MVP


"Karen" wrote in message
...
I have a column of data and the dates are in the format of 20090614.
How
can
I convert these dates so they are in the following format: 06/14/09.
ANY help would be greatly appreciated
Karen