We have a table/spreadsheet with 2 columns - Column B is supposed to be a date, but it is stored as text. I figured out how to convert the entire column to a number, but now I can't figure out how to tell Excel to read the column as MM/DD/YYYY.
Thanks in advance,
File attached.
B1 contains 10207, what date does that represent?
Audere est facere
try selecting the column and go to Data|Text to Columns..
Click Next twice and then choose Date from the column data format area and choose MDY from the neighbouring dropdown.
Click Finish.
You can then format with Cells|Format|Number choosing date.. if you don't like the format it returned.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
May be in C1
=DATE(RIGHT(B1,1)+2000,LEFT(B1,1),MID(B1,2,2)) copied down
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
sorry - that should be 1/02/07Originally Posted by daddylonglegs
OldChippy - that seemed to work perfect, thanks!
Not sure it would work for 2 digit months, though......
You could put this formula in C1
=TEXT(B1,"00-00-00")+0
format C1 as mm/dd/yy and copy down column
Last edited by daddylonglegs; 06-21-2010 at 05:16 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks