HELP with this code

[Guest]

Hi,

In worksheet 1 i have 3 fields:
A1 A2 A3
1234 1 19980101

A2 is a 3 character field, some with leading zeros and some without. A3 is
the date but i need to populate it as YYDDD. I need to populate all 3 fields
together in the following format:

123400198001

Someone suggested me i DO the following code and it worked perfect.

=IF(LEN(A2)=1,A1&"00"&A2&TEXT(A3,"yy")&IF(LEN(DAY(A3))=1,"00"&DAY(A3),"0"&DAY(A3)),IF(LEN(A2)=2,A1&"0"&A2&TEXT(A3,"yy")&IF(LEN(DAY(A3))=1,"00"&DAY(A3),"0"&DAY(A3)),IF(LEN(A2)=3,A1&A2&TEXT(A3,"yy")&IF(LEN(DAY(A3))=1,"00"&DAY(A3),"0"&DAY(A3)))))

But i forgot to mention that if A1 has any leading zeros, they should be
removed and thats not included in the code, can someone suggest me? Thanks

 

[Bob Phillips]

=TEXT(A1,"#0")&TEXT(A2,"000")&TEXT(A3,"yy")&"0"&TEXT(A3,"dd")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Simpler

=TEXT(A1,"#0")&TEXT(A2,"000")&TEXT(A3,"yy\0dd")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thank you

Simpler

=TEXT(A1,"#0")&TEXT(A2,"000")&TEXT(A3,"yy\0dd")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)