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?
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:
- Select cell E11.
- Press Ctrl+1 to display the Format Cells dialog.
- Click the Number tab and then select the Custom category. Excel will show the actual code used to generate the format in that cell.
- Highlight those characters, press Ctrl+C to copy to the Clipboard, and paste into the TEXT function.
- Change the formula in B13 to
="Please remit "&TEXT(E11,"$#,##0.00")&
" before "&TEXT(F1+F3,"dddd, mmmm d, yyyy")
- 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.