Text to dates

[Topher]

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

 

[Jacob Skaria]

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 Next>Next 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

 

[David Biddulph]

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.

 

[Topher]

Sorry but this does not work.

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 Next>Next 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

 

[Topher]

This does not solve the problem either

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




.

 

[Dave Peterson]

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

 

[Fred Smith]

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