difficulty changing cell format

[Guest]

I have pulled information from another application into an Excel spreadsheet.
The date column is in a format that I don't like & I would like to change it,
but I am having difficulty doing it. I have highlighted the column, and when
that didn't work, a single cell, and chosen a date format. The numbers in the
column don't even budge.

 

[Guest]

My guess would be that the data imported as text instead of a number, and the
date formats only work on numbers. In a separate column, try turning the
information back into numbers by using =VALUE(A1) or =A1*1, then formatting
the way you want. You can then copy and paste special...values over the
orignal data and delete the formula column.

 

[Guest]

Thanks so much for your help!

My guess would be that the data imported as text instead of a number, and the
date formats only work on numbers. In a separate column, try turning the
information back into numbers by using =VALUE(A1) or =A1*1, then formatting
the way you want. You can then copy and paste special...values over the
orignal data and delete the formula column.

 

[Guest]

Well, evidently I am doing something wrong- I am not getting this to work.

 

[Gord Dibben]

Select the column of "dates" then Data>Text to Columns.

Next>Next then "column data format". Pick "Date" and enter YMD or DMY or
whatever you think is best.

Finish.

Should be able to format now.


Gord Dibben Excel MVP