Try =DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,LEN(A1)-4)) with the data
you've got.

The reason you don't get the right answer when you type in 100206 is that
you should be typing 10/02/06
--
David Biddulph

"Cat" wrote in message
...
Yes, I know this has been covered a MILLION times yet although I've looked
through many threads, I can't seem to find one that covers this EXACTLY.
I'm importing info into Excel 2000. One of the columns is a date field..
but it is not consistent.. for example, it can read 10206 or 100206 (both
to
mean Oct 2, 2006). When I try to format it into a date (10/2/06) it turns
it
into something strange, such as 01/02/36. Don't know why this is
happening.
When I click on a seperate cell w/out any info (that is formatted to a
date
field), and type in 100206 it STILL doesn't come out right! HELP!!! I
need
this info for tomorrow!!!