You have the LEN test *inside* the LEN function. Move it outside the LEN
function. Also, your formula returns a *TEXT* string that looks like a date
but is not a true Excel data.

Try this:

=--TEXT(IF(LEN(B4)=7,"0"&B4,B4),"00\/00\/0000")

Format in the Date style of your choice for a true Excel date.

--
Biff
Microsoft Excel MVP


"TagTech" wrote in message
...
A little help please. I have data imported to Excel from a legacy app.
Some
DOB is a length of 7, e.g. 1012008 and some DOB is a length of 8, e.g.
11012008. Neither formula following is working for me:


=IF( (LEN(B4=8)),(LEFT(B4,2) &"/"& MID(B4,3,2) &"/"& RIGHT(B4,4)),
(LEFT(B4,1) &"/"& MID(B4,2,2) &"/"& RIGHT(B4,4)) )


=IF( (LEN(B4=7)), (LEFT(B4,1) &"/"& MID(B4,2,2) &"/"& RIGHT(B4,4)),
(LEFT(B4,2) &"/"& MID(B4,3,2) &"/"& RIGHT(B4,4)) )

If I test for a length of eight and the date is 11012008 I get 11/01/2008,
which is good. However, if the date is 1012008 I get 10/12/2008, which is
not good.

If I test for a length of seven and the date is 11012008 I get 1/10/2008,
which is not good. However if the date is 1012008 I get 1/01/2008, which
is
good.

It seems I only ever get the "true" formulation. Any thoughts? Thanks.