Function Problems

Keep Date Formatting When Concatenating Text in Excel

Phi Hung VoPhi Hung Vo Nov 14, 2026 868 views

Question details

The user wants to join a date with text while keeping the date in a readable format.

Keep Date Formatting When Concatenating Text in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula uses the ampersand operator to combine text with a cell containing a date.
Observed behavior
The formula displays the date's underlying serial number instead of its displayed date format.
Before you start

Identify the cell containing the date and check that it contains a real Excel date rather than text. The examples below assume the date is in cell A1.

Solution 1Recommended

Use the TEXT Function to Format the Date in the Formula

Use TEXT to specify how the date should appear before joining it with other text.

Concatenation uses the underlying date value, not the date cell's display format. TEXT converts that value to a formatted string.

1
Select the result cell

Click an empty cell, such as B1, where you want the combined text to appear.

2
Enter the formula

Type ="The date was "&TEXT(A1,"dd/mm/yyyy") and press Enter. Replace A1 with the cell containing your date.

3
Choose a long-date format

For a spelled-out month, use ="The date was "&TEXT(A1,"dd MMMM yyyy") instead.

Use the TEXT Function to Format the Date in the Formula
Regional settings: If Excel uses semicolons as function argument separators, replace the comma inside TEXT with a semicolon, for example: ="The date was "&TEXT(A1;"dd/mm/yyyy").
Format dates in WPS Spreadsheets

Join Text and Formatted Dates in WPS Spreadsheets

Use the TEXT function in WPS Spreadsheets to control how a date appears when you combine it with a text string.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and choose the workbook containing your date.
  2. 2. Select an empty cell: Click a cell such as B1, assuming the date is in A1.
  3. 3. Enter the formula: Type ="The date was "&TEXT(A1,"dd/mm/yyyy") in the selected cell and press Enter.
  4. 4. Adjust the date format: Edit the format inside TEXT to "dd MMMM yyyy" if you want the month spelled out.
Format dates inside concatenation formulasOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas and a familiar interfaceWork with spreadsheets using the free version of WPS Office
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a number when I join a date with text?

Excel stores dates as serial numbers, and the ampersand operator does not preserve the cell's display format. Wrap the date reference in TEXT, as in =TEXT(A1,"dd/mm/yyyy").

How do I display the month as a word?

Use a format such as ="The date was "&TEXT(A1,"dd MMMM yyyy") to display the day, full month name, and year.

What if Excel rejects the comma in my formula?

Your regional settings may require a semicolon between function arguments. Try ="The date was "&TEXT(A1;"dd/mm/yyyy").

Can I use the same formula in WPS Spreadsheets?

Yes. In WPS Spreadsheets, select an empty cell and enter ="The date was "&TEXT(A1,"dd/mm/yyyy"), replacing A1 with your date cell.