I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" &
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks

You can use the 'Convert Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.

--From menu Data'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.

--Hit NextNext will take you to Step 3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('DMY' )

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"Topher" wrote:

I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" &
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks

Sorry but this does not work.

"Jacob Skaria" wrote:

You can use the 'Convert Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.

--From menu Data'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.

--Hit NextNext will take you to Step 3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('DMY' )

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"Topher" wrote:

I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" &
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks

A number of options some of which *might* work:
1 Copy a blank cell, Select your data range, Edit/ Paste special/ Add
2 Select your column of data, then Data/ Text to Columns

Then format the cells appropriately.
--
David Biddulph

"Topher" wrote in message
...
I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09"
&
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks

This does not solve the problem either

"David Biddulph" wrote:

A number of options some of which *might* work:
1 Copy a blank cell, Select your data range, Edit/ Paste special/ Add
2 Select your column of data, then Data/ Text to Columns

Then format the cells appropriately.
--
David Biddulph

"Topher" wrote in message
...
I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09"
&
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks


.

That's too bad, Topher. When you are interested in solving your problem,
post back with sufficient information. Like the data you have, the steps you
followed, the results you got, and what you want instead. This problem has
been solved many times before, and I'm sure it can be for you, if you are
interested.

Regards,
Fred

"Topher" wrote in message
...
This does not solve the problem either

"David Biddulph" wrote:

A number of options some of which *might* work:
1 Copy a blank cell, Select your data range, Edit/ Paste special/ Add
2 Select your column of data, then Data/ Text to Columns

Then format the cells appropriately.
--
David Biddulph

"Topher" wrote in message
...
I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan
09"
&
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan
09
(I'll also change the sheet so that data is validated to this date
format)

Does anyone know a way I can do this?

Thanks


.

This technique has worked for me with data in the same format.

How does it not work for you?

(I'd try it again if I were you.)

Topher wrote:

I have a column of dates F6:F148, the dates have been entered in the
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" &
"21 Jan 2009".

I'd like to be able to change them all into a common date format 21 Jan 09
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks

--

Dave Peterson