If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you'll want to convert dates to text first to ensure it displays correctly.
We can use the TEXT function for this. The second argument of the text function allows us to specify the date format we want applied.
| Syntax: | =TEXT(value, format_text) |
| value | This is typically a reference to a cell containing the date serial number, but could also be a DATE formula. |
| format_text | This is the date number format you want to apply (surrounded by double quotes). |
Tip: You can use the date number formats to construct the date's appearance in many different ways. You can also concatenate additional text using the & symbol, as shown in C19 and C20 in the image below.
Note 1: my dates are formatted dd/mm/yyyy, but if you format dates mm/dd/yyyy, then you can simply change the format in the format_text argument of the TEXT formula.
Note 2: the TEXT function converts the dates to text, obviously! But bear in mind that this means you can't use the values in column C in any math formulas, or many of the Date functions.
More Excel Formatting
Custom formatting isn't limited to dates and times. There is a plethora of formatting options for all types of numbers that we can use to get our reports looking just the way we want. Click here for our comprehensive guide to Excel custom number formatting.
Dynamic Text Labels
Now that you know how to convert dates to text, why not learn how to use them in dynamic text labels in your reports and charts.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
Fix Excel Dates Formatted as Text
Leave a Reply