Function Problems

How to Combine Excel Dates with Text Without Showing Serial Numbers

Rana Garcia Oct 15, 2026 870 views

Question details

The user wants to combine a date cell with text in a spreadsheet without the date reverting to its underlying serial number format.

How to Combine an Excel Date Without Showing Its Serial Number

Product: Excel
Device & OS: not provided
Scenario: Concatenating or joining text strings with dates in spreadsheet formulas for reporting or data presentation.
Observed behavior: When a date is combined with text using standard concatenation, the date displays as a 5-digit serial number instead of the formatted date.

Before you start

Identify the target cell containing your date and decide on the exact date format (e.g., MM/DD/YYYY or YYMMDD) you want to display in the combined text string.

Solution 1Recommended

Use the Ampersand Symbol (&) with the TEXT Function

The most straightforward way to combine a date and text is by using the & operator along with the TEXT function to format the serial number as a readable date.

Excel inherently stores dates as sequential serial numbers for calculation purposes. The TEXT function forces Excel to convert this numerical value into a text string based on a specified format code.

Select the destination cell

Click on the cell where you want the combined text and date to appear.

Enter the combination formula

Type the formula combining your text and the TEXT function. For example, to combine the text in cell B1 with the date in cell A1, type =B1 & " " & TEXT(A1, "mm/dd/yyyy").

Apply the formula

Press Enter to apply the formula. The date will now display in your chosen format instead of a serial number.

Use the Ampersand Symbol (&) with the TEXT Function

Customizing Date Formats: You can change the "mm/dd/yyyy" format code to any standard Excel date format string, such as "dd-mmm-yy" or "yymmdd" as needed.

Solution 2

Use the CONCATENATE Function with TEXT

If you prefer using explicit functions for joining strings, you can nest the TEXT function inside the CONCATENATE function.

Smart Data Processing

Easily Manage Dates and Text with WPS Spreadsheet

WPS Spreadsheet fully supports the TEXT and CONCATENATE functions, allowing you to combine dates and text seamlessly without displaying serial numbers. It offers a user-friendly interface for managing complex data operations.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date values.
2. Select a target cell: Click on the destination cell for your combined data.
3. Enter the formula: Go to the Formula bar and enter your desired string, such as =A2 & " " & TEXT(B2, "yyyy-mm-dd").
4. Apply and review: Press Enter to view the correctly formatted text and date combination.

Free to use with a familiar, easy-to-navigate interface.Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in function tooltips to help you quickly construct TEXT and concatenation formulas.Lightweight software that runs smoothly on various operating systems.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my date to a 5-digit number?

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. When you combine a date with text, Excel strips the cell formatting and reveals this underlying serial number.

How do I include spaces between the text and the date in my formula?

You can add spaces by including a space character enclosed in quotation marks within your formula. For example, =A1 & " " & TEXT(B1, "mm/dd/yyyy") adds a single space between the contents of A1 and the formatted date.

Can I use the TEXT function to display the day of the week?

Yes, you can display the day of the week by using "dddd" in the format code. For example, =TEXT(A1, "dddd") will output 'Monday' if the date in A1 is a Monday.

Does this method work with times as well as dates?

Absolutely. Since times are stored as fractional serial numbers in Excel, you can use the TEXT function to format them. For example, use =TEXT(A1, "hh:mm AM/PM") before concatenating it with your text.

Excel & Data Problems