Formatted Excel Date Within Text

Q: Want to calculate a date from some cells and then combine it with literal text in another cell. If I enter something like ="Totals thru " & MAX(C21:C100), the result doesn't display correctly. I get something like " Totals thru 38463 ", when what I really want is something like " Totals thru April 21, 2005 ". How can I do this?

A: For the solution,
1. First, you'll need to determine the format mask corresponding to the date format you want. The easiest way is to start with a cell that contains a date that's formatted that way.
2. Right-click the cell, choose Format Cells,
3. click the Number tab, and select Custom in the Category list at left.
4. Now highlight the text in the box titled Type and copy it to the clipboard. It will look something like mmmm d, yyyy.

5. Now go back to the cell where you want to display a date combined with literal text. The TEXT() function will convert that date into text using whatever format mask you specify.
In the example shown above, it might look like this: ="Totals thru " & TEXT(MAX(C21:C100), "mmmm d, yyyy").

Of course, you can use this technique to format any type of data in combination with literal text.