ExcelBanter - change date format

Assuming the 3-digit database year is correct (and not a mistyping of yyyy),
your formula will only work if the year value is in this millennium (for
example, it will fail for 997 as the first 3-digits). Here is a modification
to your formula that will work for the any between 1/1/1900 and
12/32/2099...

=DATE((1+(LEFT(A1)="0"))&LEFT(A1,3),MID(A1,4,2),RI GHT(A1,2))

Also, here is an slightly shorter formula which also works within the same
range of years....

=--TEXT((1+(LEFT(A1)="0"))&A1,"0000-00-00")

Again, both of the above formulas assume the OP's database date really only
has a 3-digit year. If it turns out that the database date pattern was
mistyped and should have been really been yyyymmdd, then my proposed formula
would be...

=--TEXT(A1,"0000-00-00")

In all case above, your suggestion to use a Custom format to display the
converted date as mmddyyyy still holds.

Rick

"Gary''s Student" wrote in message
...
Strange to have a 3-digit year.

If A1 contains:
0081225

In cell B1 enter:
=DATE(2000+LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2))
and format Custom as mmddyyyy to display
12252008

--
Gary''s Student - gsnu200772

"linda" wrote:

I have a database that has a date format of yyymmdd. I need this to be
mmddyyyy. How can I change this?

--
linda