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

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

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

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

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

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

Hi,

Make sure you don't select fixed width in step 1 and then don't click
anywhere in the Preview pane in step 2.

Also, is 20090614 what you see displayed in the cell or on the Formula Bar.
Maybe the formula bar shows something different than the cell and you are
splitting on a delimiter?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Karen" wrote:

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

Thank you for your help. Yes, 20090614 is what I see in the cell and in the
formula bar. I tried what you mentioned and it didn't do anything. I may be
doing something wrong.
Karen

"Shane Devenshire" wrote:

Hi,

Make sure you don't select fixed width in step 1 and then don't click
anywhere in the Preview pane in step 2.

Also, is 20090614 what you see displayed in the cell or on the Formula Bar.
Maybe the formula bar shows something different than the cell and you are
splitting on a delimiter?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Karen" wrote:

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

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.

With the original data in column A, one way is to put this in column B and
copy down:
=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
Use any date format for the display.

If you need to get rid of column A, select column B and use
Edit Copy
Edit Paste special Values
Then delete column A.