Formula Errors

Fix Date Serial Numbers in Excel Concatenated Text

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

Display a formatted date instead of its serial number when combining a date with text in a formula.

Fix Date Serial Numbers in Excel Concatenated Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user combines a date cell with other text in an Excel formula.
Observed behavior
The combined text shows a five-digit date serial number even though the source date cell displays correctly.
Before you start

Identify the cell containing the original date and the formula that combines it with text. Confirm that the source cell contains a valid Excel date value rather than an unrecognized text string.

Solution 1Recommended

Format the date inside the concatenation formula

Use TEXT to convert the date value into the desired display format before combining it with other text.

1
Select the formula cell

Click the cell that displays the combined text and select its formula in the formula bar.

2
Wrap the date reference

Replace the date reference with TEXT and a format code. For example, use ="Date: "&TEXT(E4,"mmmm dd, yyyy") to combine a label with the date in E4.

3
Apply the formula

Press Enter, then use the fill handle to copy the formula to other rows if needed.

Format the date inside the concatenation formula
Choose a date format: Change the format code to suit your output, such as "mm/dd/yyyy" for a numeric date. Formatting the source cell alone does not control how the date appears inside concatenated text.
WPS Spreadsheets

Format Dates in Combined Text with WPS Spreadsheets

Use the TEXT function in WPS Spreadsheets to combine a date with a label while displaying the date in a readable format.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and select File > Open to load your workbook.
  2. 2. Select the result cell: Click the cell where you want to combine the date with text.
  3. 3. Enter the formula: Type ="Date: "&TEXT(E4,"mmmm dd, yyyy"), replacing E4 with your date cell, and press Enter.
  4. 4. Copy the formula: Drag the cell's fill handle down to apply the formula to other rows.
Format dates within concatenation formulas using TEXTOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface to fill formulas across rowsCopy formatted results as values for use in documents
microsoft office alternative - wps office

Frequently Asked Questions

Why does my date become a five-digit number when I concatenate it?

Excel stores dates as serial numbers. When a formula combines a date with text, the source cell's display format is not carried into the result. Use TEXT, such as TEXT(E4,"mmmm dd, yyyy"), inside the formula.

Can I use a different date format in the formula?

Yes. Change the format code in TEXT. For example, =TEXT(E4,"mm/dd/yyyy") displays the date as a numeric month, day, and year.

How do I display a standalone serial number as a date?

If the cell contains a numeric serial number, select it and choose a date format from Home > Number Format. If you need text for a concatenation formula, use TEXT instead.

What if I already copied the serial numbers into a Word document?

If you still have the source dates, apply TEXT in Excel and copy the corrected results into Word. If the numbers are embedded in edited sentences, identify and verify each date before replacing the corresponding number manually.