When concatenating—using a formula to combine data and/or
text from one or more cells into one cell—date cells with text cells in an
Excel formula, you must first convert the referenced date cells to text;
otherwise, you end up with an unexpected result. For example, suppose cell H11
contained the text Due Date: and cell H12 contained a formula that calculated
the date. H12 is correctly formatted for the date data type, m/d/yyyy. If you use the formula =H11&H12 to concatenate
these cells, the result comes back with the serial date (such as, Due Date:
39054).
Because Excel ignores the formatting of H12, Excel returns
the serial date unless the contents of H12 are converted to text before
concatenating, as shown in the following formula:
=H11&TEXT(H12,” mmmm d, yyyy”)
The correct result of this formula is Due Date: December 3,
2006.
Miss a tip?
Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
