Excel: Join Text with a Date or Currency

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I just learned about concatenation, and I'm trying to join text with currency and with a date. As you can see in cell B13, when I attempt to join both date and currency with text, the currency loses the dollar sign and the date appears as a strange number. What am I doing wrong?

  1. The formula in B13 fails miserably.

    Strategy: Excel internally stores dates as numbers and relies on the number format to display the number as a date. In the formula, you can use the TEXT function to convert a date or a number into text with a particular numeric format. For example, the formula =TEXT(F1+F3,"mm/dd/yyyy") would produce the text 07/18/2014. Thanks to the variety of custom number formats, you could also use =TEXT(F1+F3,"dddd, mmmm d, yyyy") to create the text Friday, July 18, 2014.

    Additional Details: If you are not sure of the actual custom number format codes, you can query them from any existing cell. Here's an example:

    1. Select cell E11.
    2. Press Ctrl+1 to display the Format Cells dialog.
    3. Click the Number tab and then select the Custom category. Excel will show the actual code used to generate the format in that cell.
    4. Highlight those characters, press Ctrl+C to copy to the Clipboard, and paste into the TEXT function.
    5. Change the formula in B13 to

    ="œPlease remit "œ&TEXT(E11,"$#,##0.00")&

    " before "œ&TEXT(F1+F3,"dddd, mmmm d, yyyy")

  2. Use the TEXT function to format dates and currency.

    Additional Details: Excel stores dates as the number of days elapsed since January 1, 1900 (on a PC), or since January 1, 1904 (on a Mac). The 41838 shown in cell B13 of Fig 224 corresponds to July 18, 2014. While this is a fascinating bit of information (if you are Cliff Claven), I've never had a manager call and ask, "œHey, how many days after January 1, 1900, is that receivable due?" This method makes it easy for Excel to calculate differences between two dates.