date format help needed please

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: date format help needed please by David

David
Sat Aug 16 12:05:53 CDT 2008

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

"Mominator" <Mominator@discussions.microsoft.com> wrote in message
news:BFF7057C-3427-4D3E-880D-BA194AE6F93A@microsoft.com...
> 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
>


Top

Re: date format help needed please by Mominator

Mominator
Sat Aug 16 14:40:01 CDT 2008

THANK YOU!!!
You saved my sanity.

"David Biddulph" wrote:

> =IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))
> Adjust to suit the format of your text input.
> --
> David Biddulph
>
> "Mominator" <Mominator@discussions.microsoft.com> wrote in message
> news:BFF7057C-3427-4D3E-880D-BA194AE6F93A@microsoft.com...
> > 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
> >
>
>
>
Top