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),RIGHT(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
