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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nick" wrote in message
...
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"&DA
Y(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(LE N(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

Simpler

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nick" wrote in message
...
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"&DA
Y(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(LE N(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

Thank you

"Bob Phillips" wrote:

Simpler

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nick" wrote in message
...
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"&DA
Y(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(LE N(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