This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Join Text with a Date or Currency
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?
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")
Figure 226 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 Figure 225 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.