date format help needed please
 

sorry to post twice- but not sure where this question belongs...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

date format help needed please
 

=IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),LEFT(A1,2),MI D(A1,3,2)))
Adjust to suit the format of your text input.
--
David Biddulph

"Mominator" wrote in message
...
sorry to post twice- but not sure where this question belongs...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

date format help needed please
 

THANK YOU!!!
You saved my sanity.

"David Biddulph" wrote:

=IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),LEFT(A1,2),MI D(A1,3,2)))
Adjust to suit the format of your text input.
--
David Biddulph

"Mominator" wrote in message
...
sorry to post twice- but not sure where this question belongs...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