Function Problems

How to Join a Formatted Date and Text in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to combine a date formatted as yyyy-mm-dd with a static text string (T00:00:00) in a single cell without losing the date format.

Product: Excel
Device & OS: not provided
Scenario: Formatting dates for data export or standardizing timestamp strings in reports.
Observed behavior: Requires a specific formula to properly display the joined date and text, as directly combining a date cell with text often converts the date into a raw serial number.

Before you start

Ensure the cell containing your original date is recognized as a valid date value in Excel, rather than plain text, so the TEXT function can format it correctly.

Solution 1Recommended

Use the TEXT Function with the Ampersand (&) Operator

The most effective method to combine a date with text while retaining a specific date format is by utilizing the TEXT function alongside the ampersand concatenation operator.

In Excel, dates are stored as sequential serial numbers. If you try to join a date cell and a text string directly, Excel will output the raw serial number instead of the formatted date. Using the TEXT function resolves this by converting the numeric date value into a formatted text string before joining it.

Select the destination cell

Click on the cell where you want the combined date and text string to be displayed.

Start the TEXT function

Type =TEXT(D5, "yyyy-mm-dd") assuming your source date is in cell D5. This converts the date into the desired year-month-day text format.

Append the text string

Immediately following the TEXT formula, add the ampersand (&) operator followed by your text enclosed in quotation marks: &"T00:00:00".

Apply the completed formula

Ensure your entire formula reads =TEXT(D5,"yyyy-mm-dd")&"T00:00:00" and press Enter to see the correctly formatted result.

Customizing the Formula: You can modify 'yyyy-mm-dd' to any valid date format string (such as 'mm/dd/yyyy') and replace 'T00:00:00' with any specific text you need to append.

Powerful Spreadsheet Editor

Easily Manage Formulas and Formatting with WPS Spreadsheet

WPS Spreadsheet perfectly supports all standard Excel functions, including the TEXT formula and concatenation operators. You can quickly manipulate dates, numbers, and text strings with the same familiar formulas.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
2. Enter your data: Make sure your base dates are entered into a column and formatted as standard dates.
3. Apply the concatenation formula: Type the exact formula =TEXT(cell, "format")&"text" to seamlessly combine your dates and static text strings.

100% compatible with Microsoft Excel formulas and date formattingLightweight, fast, and responsive even with complex datasetsIntuitive formula builder with real-time syntax suggestionsCompletely free to use for daily data processing tasks

microsoft office alternative - wps office

Frequently Asked Questions

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

Excel stores dates internally as serial numbers (e.g., 44562). If you concatenate a date cell directly with text without converting it first, Excel reveals that underlying raw number. Wrapping the date cell in the TEXT function forces Excel to apply a readable format before joining it to the text.

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

Yes, the CONCATENATE or CONCAT function works exactly the same way. The alternative formula would be =CONCATENATE(TEXT(D5, "yyyy-mm-dd"), "T00:00:00"). However, utilizing the ampersand (&) is typically much faster to type.

How do I add a space between the formatted date and the appended text?

To add a space, simply include it inside the quotation marks of your appended text string. For example, modify the formula to =TEXT(D5, "yyyy-mm-dd") & " T00:00:00" with a space right before the 'T'.

Excel & Data Problems