I have a sheet that has many rows with a date that looks like a text format.
If I right click on the cell I can check it to a number and then to an
mm/dd/yyyy. My question is can I change all the rows to this format is one
shot. I tried to choose the whole column and do a change format but that
didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,

I do not understand the first part of message, but
My question is can I change all the rows to this format is one shot. I
tried to choose the whole column and do a change format but that didn't
work. How would I go about changing mmddyyyy to mm/dd/yyyy?
is readily answered:
Let you data be in A1:A100
If need be, insert a new column B (we can remove it later)
In B1 use =DATE(YEAR(RIGHT(A1,4)),MONTH(MID(A1,3,2)),LEFT(A1 ,2))
Format date to show as required
Copy down to B100 by double clicking B1's fill handle (little solid square
in lower right corner)
Now to tidy up:
Select B1:B100 and copy; without moving the selection use Edit| Paste
Special- Values to convert formulas to date values
Now you can delete column A or copy B1:B100 to A1:A100 and delete column B
best wishes

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Peretz Stern" wrote in message
...
I have a sheet that has many rows with a date that looks like a text
format. If I right click on the cell I can check it to a number and then to
an mm/dd/yyyy. My question is can I change all the rows to this format is
one shot. I tried to choose the whole column and do a change format but
that didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,

See my answer in microsoft.public.excel.
Please do not multi-post; most of us read all the Excel groups
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Peretz Stern" wrote in message
...
I have a sheet that has many rows with a date that looks like a text
format. If I right click on the cell I can check it to a number and then to
an mm/dd/yyyy. My question is can I change all the rows to this format is
one shot. I tried to choose the whole column and do a change format but
that didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,

If the data is in a single column, you can use:

Select the column/range
Data|text to column
fixed width (but remove any lines and don't add any more)
choose mdy as the field format

Tnen format that range the way you like (mm/dd/yyyy).

Peretz Stern wrote:

I have a sheet that has many rows with a date that looks like a text format.
If I right click on the cell I can check it to a number and then to an
mm/dd/yyyy. My question is can I change all the rows to this format is one
shot. I tried to choose the whole column and do a change format but that
didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?

Thanks,

--

Dave Peterson

If you truly have your present data as mmddyyy,
including the leading zeroes, you could try TTC.

Select the column of text dates, then:

<Data <Text To Columns <Next <Next
Under "Column Data Format", click on "Date",
And make sure "MDY" shows in the window.

Then <Finish.

This changes the column to true XL dates, where you can now simply format
the column to whatever date display you wish.
--
HTH,

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



"Peretz Stern" wrote in message
...
I have a sheet that has many rows with a date that looks like a text
format.
If I right click on the cell I can check it to a number and then to an
mm/dd/yyyy. My question is can I change all the rows to this format is one
shot. I tried to choose the whole column and do a change format but that
didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,

Thanks! it worked like a charm.


"RagDyer" wrote in message
...
If you truly have your present data as mmddyyy,
including the leading zeroes, you could try TTC.

Select the column of text dates, then:

<Data <Text To Columns <Next <Next
Under "Column Data Format", click on "Date",
And make sure "MDY" shows in the window.

Then <Finish.

This changes the column to true XL dates, where you can now simply format
the column to whatever date display you wish.
--
HTH,

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



"Peretz Stern" wrote in message
...
I have a sheet that has many rows with a date that looks like a text
format.
If I right click on the cell I can check it to a number and then to an
mm/dd/yyyy. My question is can I change all the rows to this format is
one
shot. I tried to choose the whole column and do a change format but that
didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peretz Stern" wrote in message
...
Thanks! it worked like a charm.


"RagDyer" wrote in message
...
If you truly have your present data as mmddyyy,
including the leading zeroes, you could try TTC.

Select the column of text dates, then:

<Data <Text To Columns <Next <Next
Under "Column Data Format", click on "Date",
And make sure "MDY" shows in the window.

Then <Finish.

This changes the column to true XL dates, where you can now simply format
the column to whatever date display you wish.
--
HTH,

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



"Peretz Stern" wrote in message
...
I have a sheet that has many rows with a date that looks like a text
format.
If I right click on the cell I can check it to a number and then to an
mm/dd/yyyy. My question is can I change all the rows to this format is
one
shot. I tried to choose the whole column and do a change format but that
didn't work. How would I go about changing mmddyyyy to mm/dd/yyyy?



Thanks,