Formatting Cells Containing Date Fields That Are Text

[Sheldon]

Someone here at work imported into Excel a column of text data that
looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could
simply highlight the columns, click on Format/Cells/Custom and create
mmddyyyy. I do this but the data doesn't change UNTIL I highlight
each cell and press F2 or double-click in each cell or create a
formula using Right, Mid, etc. When I press F2 or double-click, I
notice that an X and a check mark both appear to the right of the Row/
Column box. There's got to be an easier way to do this besides
'onesie-twosie' but we can't figure it out. Suggestions?
Thanks, Sheldon Potolsky

 

[Scoops]

Someone here at work imported into Excel a column of text data that
looks like mm/dd/yyyy form but need to be mmddyyyy.  I thought I could
simply highlight the columns, click on Format/Cells/Custom and create
mmddyyyy.  I do this but the data doesn't change UNTIL I highlight
each cell and press F2 or double-click in each cell or create a
formula using Right, Mid, etc.  When I press F2 or double-click, I
notice that an X and a check mark both appear to the right of the Row/
Column box.  There's got to be an easier way to do this besides
'onesie-twosie' but we can't figure it out.  Suggestions?
Thanks, Sheldon Potolsky

Hi Sheldon

If your data where to be in column A then in a free column:

=TEXT(A1,"mmddyyyy")

Copy down as necessary
Copy the new values
PasteSpecial the Values over the imported data
Delete the formulas

Regards

Steve

 

[Gord Dibben]

If an F2 and ENTER gives you good dates try this..............

Format all to General.. Coipy an empty cell.

Select the range of Dates and Paste Special>Add>OK>Esc.

Re-format the dates.


Gord Dibben MS Excel MVP

 

[Sheldon]

Thank you Steve and Gord; I tested each of your solutions
successfully.
Sheldon