Join Text with a Date or Currency

December 13, 2021 - by Bill Jelen

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?

A formula is trying to create a message at the bottom of an invoice to remit the amount due by a due date. But the message shows the amount in a funny was of 14975.2 and the date as 45856. — Figure 218. 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/2025. 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, 2025.

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

In the previous formula to concatenate a message, refer to E11 as TEXT(E11,"$#,##0.00") and refer to the due date as TEXT(F1+F3,"dddd, mmmm d, yyyy"). Now the message reads Please remit $14,975.20 before Friday, July 18, 2025. — Figure 219. 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 45856 shown in cell B13 of Figure 218 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Giorgio Trovato on Unsplash