Changing to date format

[Peretz Stern]

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,

 

[Bernard Liengme]

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

 

[Bernard Liengme]

See my answer in microsoft.public.excel.
Please do not multi-post; most of us read all the Excel groups

 

[Dave Peterson]

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).

 

[RagDyer]

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]

Thanks! it worked like a charm.

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!
==============================================

 

[Ragdyer]

Appreciate the feed-back.