Text to dates

T

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
 
J

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
 
D

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

Topher

Sorry but this does not work.

Jacob Skaria said:
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
 
T

Topher

This does not solve the problem either

David Biddulph said:
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




.
 
D

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top