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.

You have the bracker for the LEN function in the wrong place.
Try this:
=IF( (LEN(B4)=8),(LEFT(B4,2) &"/"& MID(B4,3,2) &"/"&
RIGHT(B4,4)),(LEFT(B4,1) &"/"& MID(B4,2,2) &"/"& RIGHT(B4,4)) )
There are still more brackets than necessary, BTW

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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.

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.

"T. Valko" wrote...
....
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".

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
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".

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

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


--
Biff
Microsoft Excel MVP

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!

"Niek Otten" wrote:

You have the bracker for the LEN function in the wrong place.
Try this:
=IF( (LEN(B4)=8),(LEFT(B4,2) &"/"& MID(B4,3,2) &"/"&
RIGHT(B4,4)),(LEFT(B4,1) &"/"& MID(B4,2,2) &"/"& RIGHT(B4,4)) )
There are still more brackets than necessary, BTW

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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.