software

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,A1amp;quot;00quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN(DAY( A3))=1,quot;00quot;amp;DAY(A3),quot;0quot;amp;DAY(A3)),IF(LEN(A2)=2,A1amp;quot; 0quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN(DAY(A3))=1,quot;00quot;amp;DAY(A3) ,quot;0quot;amp;DAY(A3)),IF(LEN(A2)=3,A1amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF( LEN(DAY(A3))=1,quot;00quot;amp;DAY(A3),quot;0quot;amp;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,quot;#0quot;)amp;TEXT(A2,quot;000quot;)amp;TEXT(A3,quot;yyquot;)amp;quot;0quot;amp;TE XT(A3,quot;ddquot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;nickquot; gt; wrote in message
...
gt; Hi,
gt;
gt; In worksheet 1 i have 3 fields:
gt; A1 A2 A3
gt; 1234 1 19980101
gt;
gt; A2 is a 3 character field, some with leading zeros and some without. A3
is
gt; the date but i need to populate it as YYDDD. I need to populate all 3
fields
gt; together in the following format:
gt;
gt; 123400198001
gt;
gt; Someone suggested me i DO the following code and it worked perfect.
gt;
gt;
=IF(LEN(A2)=1,A1amp;quot;00quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN(DAY( A3))=1,quot;00quot;amp;DAY(A3),quot;0quot;amp;DA
Y(A3)),IF(LEN(A2)=2,A1amp;quot;0quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN (DAY(A3))=1,quot;00quot;amp;DAY(A3),quot;
0quot;amp;DAY(A3)),IF(LEN(A2)=3,A1amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LE N(DAY(A3))=1,quot;00quot;amp;DAY(A3),
quot;0quot;amp;DAY(A3)))))
gt;
gt; But i forgot to mention that if A1 has any leading zeros, they should be
gt; removed and thats not included in the code, can someone suggest me? Thanks
gt;
gt;
Simpler

=TEXT(A1,quot;#0quot;)amp;TEXT(A2,quot;000quot;)amp;TEXT(A3,quot;yy\0ddquot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;nickquot; gt; wrote in message
...
gt; Hi,
gt;
gt; In worksheet 1 i have 3 fields:
gt; A1 A2 A3
gt; 1234 1 19980101
gt;
gt; A2 is a 3 character field, some with leading zeros and some without. A3
is
gt; the date but i need to populate it as YYDDD. I need to populate all 3
fields
gt; together in the following format:
gt;
gt; 123400198001
gt;
gt; Someone suggested me i DO the following code and it worked perfect.
gt;
gt;
=IF(LEN(A2)=1,A1amp;quot;00quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN(DAY( A3))=1,quot;00quot;amp;DAY(A3),quot;0quot;amp;DA
Y(A3)),IF(LEN(A2)=2,A1amp;quot;0quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN (DAY(A3))=1,quot;00quot;amp;DAY(A3),quot;
0quot;amp;DAY(A3)),IF(LEN(A2)=3,A1amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LE N(DAY(A3))=1,quot;00quot;amp;DAY(A3),
quot;0quot;amp;DAY(A3)))))
gt;
gt; But i forgot to mention that if A1 has any leading zeros, they should be
gt; removed and thats not included in the code, can someone suggest me? Thanks
gt;
gt;
Thank you

quot;Bob Phillipsquot; wrote:

gt; Simpler
gt;
gt; =TEXT(A1,quot;#0quot;)amp;TEXT(A2,quot;000quot;)amp;TEXT(A3,quot;yy\0ddquot;)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;nickquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; In worksheet 1 i have 3 fields:
gt; gt; A1 A2 A3
gt; gt; 1234 1 19980101
gt; gt;
gt; gt; A2 is a 3 character field, some with leading zeros and some without. A3
gt; is
gt; gt; the date but i need to populate it as YYDDD. I need to populate all 3
gt; fields
gt; gt; together in the following format:
gt; gt;
gt; gt; 123400198001
gt; gt;
gt; gt; Someone suggested me i DO the following code and it worked perfect.
gt; gt;
gt; gt;
gt; =IF(LEN(A2)=1,A1amp;quot;00quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN(DAY( A3))=1,quot;00quot;amp;DAY(A3),quot;0quot;amp;DA
gt; Y(A3)),IF(LEN(A2)=2,A1amp;quot;0quot;amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LEN (DAY(A3))=1,quot;00quot;amp;DAY(A3),quot;
gt; 0quot;amp;DAY(A3)),IF(LEN(A2)=3,A1amp;A2amp;TEXT(A3,quot;yyquot;)amp;IF(LE N(DAY(A3))=1,quot;00quot;amp;DAY(A3),
gt; quot;0quot;amp;DAY(A3)))))
gt; gt;
gt; gt; But i forgot to mention that if A1 has any leading zeros, they should be
gt; gt; removed and thats not included in the code, can someone suggest me? Thanks
gt; gt;
gt; gt;
gt;
gt;
gt;