|
If the second column is all in those format with an 11 digit date you can use a formula to trim the value down to just the first 11 digits. So if in A1 you had:
Jan 06 2010 01:30PM
then in cell B1 put this formula:
=LEFT(A,11)
That will result in A2 having a value of Jan 06 2010. Copy the formula down the column. Then copy column B (select column, control-c) and then paste into column C as values only (select column, right click, paste special, values, ok). Delete columns A and B as unnecessry.
So now you have a column that is just a text-formatted date. To convert the text into a date format (this is messy but it will work):
In cell A2 copy this formula:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(G1,"Jan ","01/"),"Feb ","02/"),"Mar ","03/"),"Apr ","04/"),"May ","05/"),"Jun ","06/"),"Jul ","07/"),"Aug ","08/"),"Sep ","09/"),"Oct ","10/"),"Nov ","11/"),"Dec ","12/")," ","/"))
This will result in a five digit number that is an Excel date code.
Copy Column B into Column C as values only (see above). Now change the date format for the column to whatever date format you want and you should be good.
That messy formula assumes the month abbreviations are
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
If they aren't the formula will need to be tweaked.
|