Formatting Issues

Display an Excel Date Instead of a Five-Digit Serial Number in Text

Elise WilliamsElise Williams Nov 12, 2026 869 views

Question details

Format a date as readable text when it is combined with other text in an Excel formula.

Display an Excel Date Instead of a Five-Digit Serial Number in Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user is building a text formula that includes a check number and a received date.
Observed behavior
The date displays as its underlying five-digit Excel serial number instead of a formatted calendar date.
Before you start

Confirm that the referenced date cell contains a real Excel date value, not plain text. Choose the output format you want to display, such as dd/mm/yyyy or mm/dd/yyyy, before editing the formula.

Solution 1Recommended

Use the TEXT Function to Format the Date Inside the Formula

Use this method when a formula joins a date with text and the date appears as a serial number.

Excel stores dates as serial values. When a date is concatenated with text, use TEXT to convert that value into a readable date format in the formula result.

1
Select the formula result cell

Click the cell that contains, or should contain, the combined text and date result.

2
Edit the formula in the formula bar

Click the formula bar or press F2, then wrap the date reference in the TEXT function.

3
Apply a date format code

Enter a formula such as ="Check Number: "&check&" was received on "&TEXT(date,"dd/mm/yyyy"). Replace check and date with your cell references or named ranges.

4
Confirm the formula

Press Enter. The formula displays the date in the selected format instead of showing its serial number.

Use the TEXT Function to Format the Date Inside the Formula
Date displayed as text: The TEXT function controls how the date looks in the combined result. Use "mm/dd/yyyy", "dd-mmm-yyyy", or another valid date format code to match your preferred style.
Format dates in WPS Spreadsheet

Show Readable Dates in WPS Spreadsheet Text Formulas

WPS Spreadsheet supports the TEXT function, so you can combine labels, values, and dates while displaying the date in a clear format rather than as its underlying serial value.

  1. 1. Open the worksheet in WPS Spreadsheet: Open your .xlsx or .xls file, then select the cell where the text-and-date formula is displayed.
  2. 2. Enter formula edit mode: Click the formula bar or press F2 to edit the existing formula.
  3. 3. Add the TEXT function to the date reference: Use a formula such as ="Received on "&TEXT(A2,"dd/mm/yyyy"), replacing A2 with the cell that contains the date.
  4. 4. Choose the required date format: Change the format code inside quotation marks, for example to "mm/dd/yyyy" or "dd-mmm-yyyy".
  5. 5. Press Enter to apply the formula: Press Enter and verify that the cell now shows a readable date within the text result.
Use TEXT to format dates within concatenated formula results.Choose date displays such as dd/mm/yyyy, mm/dd/yyyy, or dd-mmm-yyyy.Open and edit Microsoft Excel .xlsx and .xls workbooks with compatible formulas.Use a familiar spreadsheet interface for quick formula editing.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel date turn into a five-digit number when I combine it with text?

Excel stores dates as serial numbers. When you join a date to text with & or CONCAT, Excel may return that underlying number unless you format the date with TEXT.

What formula displays a date as dd/mm/yyyy with other text?

Use TEXT around the date reference, for example: ="Received on "&TEXT(A2,"dd/mm/yyyy"). Replace A2 with the cell containing your date.

Can I use a different date format in the TEXT function?

Yes. Change the second TEXT argument to a format such as "mm/dd/yyyy", "yyyy-mm-dd", or "dd-mmm-yyyy". For example, =TEXT(A2,"dd-mmm-yyyy").

Does TEXT change the original date value in Excel?

No. TEXT only changes how the date appears in the formula result. The original referenced cell remains unchanged.

Can WPS Spreadsheet open formulas that use Excel TEXT date formatting?

Yes. WPS Spreadsheet supports TEXT formulas and can open and edit compatible Microsoft Excel .xlsx and .xls files.