Function Problems

Combine Text and a Formatted Date in an Excel Formula

Kushani Nimanthika Oct 10, 2026 868 views

Question details

The user needs to combine a text string with a date value in a spreadsheet without the date reverting to its underlying serial number.

Combine Text and a Formatted Date in an Excel Formula

Product: Excel / Spreadsheets
Device & OS: not provided
Scenario: Creating a formula to concatenate text and date cells to generate an output like "Yes on 9/20/2022".
Observed behavior: Standard concatenation methods like CONCAT or the ampersand (&) operator strip the date format and display the date as a 5-digit serial number instead.

Before you start

Ensure the cell containing your date is recognized as a valid date value in your spreadsheet, rather than a plain text string, before applying formatting functions.

Solution 1Recommended

Use the TEXT Function to Combine Text and Date

The TEXT function converts the date serial number into a formatted text string, allowing seamless concatenation with other text.

Spreadsheets store dates as sequential numbers for calculation purposes. By using the TEXT function, you can dictate exactly how that number should be displayed as text before joining it to another string.

Select the target cell

Click on the cell where you want the combined text and date to appear (for example, cell C1).

Enter the concatenation formula

Type the formula ="Yes on "&TEXT(B1,"m/d/yyyy") into the formula bar. Replace "Yes on " with your desired text and B1 with the reference to your date cell.

Apply and copy the formula

Press Enter to apply the formula. If you have multiple rows of data, click and drag the fill handle at the bottom-right corner of the cell to copy the formula down as needed.

Use the TEXT Function to Combine Text and Date

Customizing Date Formats: You can customize the date format by changing the "m/d/yyyy" portion of the formula to other format codes, such as "dd-mmm-yyyy" or "mmmm d, yyyy", depending on your specific regional preferences.

Effortless Data Management

Easily Format and Combine Data with WPS Spreadsheet

WPS Spreadsheet provides full support for the TEXT function and other formula operations, allowing you to combine text and formatted dates just like Microsoft Excel. It offers a powerful, familiar interface for managing your datasets efficiently.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your text and date columns.
2. Enter the formula: Select an empty cell and input your formula using the TEXT function, for example: ="Date: "&TEXT(A1,"mm/dd/yyyy").
3. View the result: Press Enter to instantly view the correctly formatted text and date combination, then drag to fill other cells.

Fully compatible with Microsoft Excel formulas and functions, including TEXT and CONCATENATE.Free and lightweight alternative for processing complex spreadsheets and data reports.Familiar interface ensures a seamless transition with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date turn into a 5-digit number when combining text?

Spreadsheet software stores dates as sequential serial numbers to easily perform calculations (e.g., the number 44824 represents Sept 20, 2022). When you combine a date with text using standard concatenation, the display formatting is stripped, and only the underlying serial number is shown unless you use the TEXT function.

Can I use the CONCATENATE function instead of the ampersand (&)?

Yes, you can use the CONCATENATE or CONCAT function alongside the TEXT function. The formula would simply look like this: =CONCATENATE("Yes on ", TEXT(B1,"m/d/yyyy")). This achieves the exact same result as using the ampersand.

How do I include the time along with the formatted date?

You can expand the format code within the TEXT function to include time elements. For example, change the formula to use =TEXT(B1, "m/d/yyyy h:mm AM/PM") to display both the date and the specific time in your combined text string.

Excel & Data Problems