dropped leading zeros returned

I have a column of mixed format.... some numbers and some are brought in as
numbers stored as text. I need to apply a date format of mmddyyy
however, when I apply to column, the values that were numbers stored as text
display the error code: ########### and the pop up tip reads "dates and
times that are negative or too large display as #######".
I have tried formatting the numbers stored as text into number values before
I apply the date value, but excel drops the leading zero's and that is
unacceptable for the reports I need to submit.
Any help would be greatly appreciated....
Thank you very much.
Candice

Solution found. ...David Biddulph solved this for me so a special thanks to
David. Thanks to all for you time on these boards. It is great to know I
can come here to get help.
Candice

"Mominator" wrote:

> I have a column of mixed format.... some numbers and some are brought in as
> numbers stored as text. I need to apply a date format of mmddyyy
> however, when I apply to column, the values that were numbers stored as text
> display the error code: ########### and the pop up tip reads "dates and
> times that are negative or too large display as #######".
> I have tried formatting the numbers stored as text into number values before
> I apply the date value, but excel drops the leading zero's and that is
> unacceptable for the reports I need to submit.
> Any help would be greatly appreciated....
> Thank you very much.
> Candice