Hi - Dave here.
Happy Friday!
You may not realize it, but today is 45429. That is, May 17, 2024, is stored as the serial number 45429 by Excel. It is only by applying number formatting that Excel will display this number as a date.
Number formats in Excel are very common, and you probably use them every day to control the display of numbers in Excel. However, if you try to join a date to a text string in Excel, you'll lose the number formatting. For example, with the date 17-May-2024 in cell A1, the formula below returns "The date is 45429":
="The date is "&A1
How can you join a date to a text string and keep the formatting? The solution is to use the TEXT function in the formula, as seen below. The formula in cell D5 is:
="The date is "&TEXT(B5,"dddd, mmmm d, yyyy")
The TEXT function returns the
formatted number as a text value. As the formula is copied down, it returns the same result for each date in column B.
[
Download the workbook and read the full explanation]
Excel's number formats are quite flexible so it is easy to display the same date in many different ways, just by adjusting formatting codes. The attached worksheet has the example above on Sheet1. On Sheet2, I've included a different example to show how to adjust codes to display a date in different ways. Download the worksheet and try it out yourself.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
