Help with Formatting
 

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
How do i do this? ANy help? Thanks

Help with Formatting
 

=A1&TEXT(A2,"000")&A3

--
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
How do i do this? ANy help? Thanks

Help with Formatting
 

Hi Nick,

Posted this earlier, but it hasn't appeared

=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)))))

Hope this helps you on your way

Ewan.

"nick" wrote:

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
How do i do this? ANy help? Thanks

Help with Formatting
 

thank you

"Bob Phillips" wrote:

=A1&TEXT(A2,"000")&A3

--
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
How do i do this? ANy help? Thanks

Help with Formatting
 

Thank you very much

"ewan7279" wrote:

Hi Nick,

Posted this earlier, but it hasn't appeared

=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)))))

Hope this helps you on your way

Ewan.

"nick" wrote:

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
How do i do this? ANy help? Thanks