Hi Richard,
I'd appreciate your expertise. I cannot nail the formula to
join a text string in cell A1 with a date format of YYYYMMDD in B1. Currently,I get an 'internal' number of sorts.  Cheers
 

---

Answer from Richard Rost:


This is a tricky one. If you had a text value like "abc" in cell A1 and then a date value like "5/1/2010" in B1, you could just use this formula to concatenate them:

=A1&YEAR(B1)&MONTH(B1)&DAY(B1)

However, you're going to only see 1-digit months and days where applicable, like:

abc201051

So now you're going to have to throw in an IF function to add an extra zero in there. I'll put this in cell C1:

=IF(MONTH(B1)<10,"0"&MONTH(B1),MONTH(B1))

Likewise for D1:

=IF(DAY(B1)<10,"0"&DAY(B1),DAY(B1))

This will give me values like 05 for the month and 01 for the date, if the result is a single digit. Now, I can add it all together:

=A1&YEAR(B1)&C1&D1

This gives me:

abc20100501

Hope this helps. Here are a couple of tutorials that will better explain some of the concepts in this answer:

http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=BlFAlEx

http://599cd.com/tips/excel/complex-if-example?key=BlFAlEx