Formatting Cells Containing Date Fields That Are Text

  • Thread starter Thread starter Sheldon
  • Start date Start date
S

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
 
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
Click to expand...

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
 
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
 
Back
Top