Is there an automatic way to change numbers such as 12312001 to da

L

LMW

I have imported a lot of dates from an old notepad file, but they came
accross as a numbers ex: December 31, 2001 is 12312001. each cell has a
number. I have formated the cells as dates, but will not recognize as a date
unless I insert / / in each cell. Is there an automatic way to change the
numbers to a date?????????
 
D

Dave Peterson

If they're all in a single column, do Data|text to columns
choose mdy as the format for that field.
plop it right back where you got it
format it the way you like.
 
C

curtev

the problem is that both solutions in this thread only work if there is a
two-digit month. if 3122004 is entered, text to columns feature will not
change to mdy format. QDE gives an error message if trying to copy data into
activated cell.

Is there a workaround for this? Thanks,
 
G

Gord Dibben

Try this formula for 7 or 8 digit numbers.

Returns March 3, 2004 from 3122004

=IF(LEN(A1)=7,DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2))),DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2))))


Watch out for word-wrap.....all one line.

Gord Dibben Excel MVP
 
Top