=IF(test,true,false) only ever returns "true"?

[TagTech]

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.

 

[Harlan Grove]

Try this:

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

Prepending 0 unnecessary.

TEXT("1232009","00\/00\/0000") and TEXT("01232009","00\/00\/0000")
both return "01/23/2009".

 

[T. Valko]

Prepending 0 unnecessary.

TEXT("1232009","00\/00\/0000") and TEXT("01232009","00\/00\/0000")
both return "01/23/2009".

That makes it even better. As long as the number format is mddyyyy or
mmddyyyy:

=--TEXT(B4,"00\/00\/0000")

 

[TagTech]

Thanks. Never would have seen that. Like a missing period in COBOL. Used
"extra" brackets to try to separate code so I could find my mistake, but as I
said, I never would have seen that it should be LEN(B4)=8 instead of
LEN(B4=8). Preciate!