Changing to date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peretz Stern - 28 Dec 2005 19:56 GMT

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,

Reply to this Message

Bernard Liengme - 28 Dec 2005 20:16 GMT

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

>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
[quoted text clipped - 3 lines]
>
> Thanks,

Reply to this Message

Bernard Liengme - 28 Dec 2005 20:19 GMT

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

Signature

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

>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
[quoted text clipped - 3 lines]
>
> Thanks,

Reply to this Message

Dave Peterson - 28 Dec 2005 20:26 GMT

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

> 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
[quoted text clipped - 3 lines]
>
> Thanks,

Signature

Dave Peterson

Reply to this Message

RagDyer - 28 Dec 2005 20:26 GMT

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.

Signature

HTH,

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

> 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
[quoted text clipped - 3 lines]
>
> Thanks,

Reply to this Message

Peretz Stern - 28 Dec 2005 20:42 GMT

Thanks! it worked like a charm.

> If you truly have your present data as mmddyyy,
> including the leading zeroes, you could try TTC.
[quoted text clipped - 18 lines]
>>
>> Thanks,

Reply to this Message

Ragdyer - 29 Dec 2005 20:13 GMT

Appreciate the feed-back.

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thanks! it worked like a charm.
>
[quoted text clipped - 20 lines]
>>>
>>> Thanks,

Reply to this Message