Function Problems

How to Preserve Time Formatting with CONCATENATE and TEXT in Excel

John WilsonJohn Wilson Nov 2, 2026 869 views

Question details

The user wants to combine a time value with a text string in a single cell without losing the readable time format.

How to Preserve Time Formatting with CONCATENATE and TEXT in Excel
Product
Excel
Device & OS
not provided
Scenario
Merging a descriptive text string with a cell containing a time value using the CONCATENATE function.
Observed behavior
Excel converts the formatted time into its underlying raw decimal serial number instead of displaying the readable time.
Before you start

Ensure that the source cell containing your time is correctly recognized as a time value, and identify the specific format (e.g., hours and minutes, AM/PM) you want to display in your final text.

Solution 1Recommended

Wrap the Time Cell in the TEXT Function

Use the TEXT function to convert the raw time serial number into a formatted text string before combining it.

Excel stores dates and times as serial numbers. A time is stored as a fractional part of a 24-hour day (for example, 12:00 PM is stored as 0.5). When you use the CONCATENATE function or the ampersand (&) to join text and time, Excel strips the visual formatting and outputs the raw decimal. To fix this, the TEXT function forces Excel to apply a specific format code and convert that number into readable text.

1
Select the destination cell

Click on the empty cell where you want the combined text and time to be displayed.

2
Start the CONCATENATE formula

Type =CONCATENATE("The event will start at ", to begin your formula. Be sure to include a space before the closing quotation mark so the text and time don't run together.

3
Add the TEXT function

Add TEXT(P10, "h:mm:ss AM/PM") to the formula, replacing 'P10' with the cell reference that contains your time value.

4
Complete and apply the formula

Add the closing parenthesis for the CONCATENATE function so the final formula looks like =CONCATENATE("The event will start at ", TEXT(P10, "h:mm:ss AM/PM")). Press Enter to see the correctly formatted result.

Wrap the Time Cell in the TEXT Function
Using the Ampersand (&) Operator: You can achieve the exact same result with a shorter formula using the ampersand symbol: ="The event will start at " & TEXT(P10, "h:mm AM/PM").
Efficient Data Processing

Easily Combine Text and Time Formats with WPS Spreadsheet

WPS Spreadsheet fully supports the CONCATENATE and TEXT functions, allowing you to combine text and formatted time values seamlessly. It provides a familiar interface and advanced formula support to manage complex data tasks effortlessly.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your text and time data.
  2. 2. Select the target cell: Click the blank cell where the combined result should appear.
  3. 3. Enter the nested formula: Type =CONCATENATE("Your text ", TEXT(A1, "h:mm AM/PM")) into the formula bar, replacing A1 with your target cell.
  4. 4. Apply the calculation: Press Enter to instantly display the perfectly formatted text string.
100% compatibility with Microsoft Excel formulas and formatsFamiliar, user-friendly interface for quick formula inputBuilt-in function wizard to help you build complex nested formulas easilyFree and lightweight alternative to expensive office suites
QA img-9

Frequently Asked Questions

Why does Excel show a decimal number instead of time when concatenating?

Excel mathematically stores times as fractional serial numbers (e.g., 6:00 PM is 0.75). When concatenating, Excel pulls this underlying raw number instead of the visual format. The TEXT function is required to convert the underlying decimal back into a formatted text string.

Can I use the TEXT function to format dates as well?

Yes, the TEXT function works identically for dates. You can wrap a date cell reference using a format string like TEXT(A1, "mm/dd/yyyy") to prevent dates from turning into 5-digit integer serial numbers during concatenation.

What if my time format string isn't displaying correctly?

Double-check the format codes inside the quotation marks. Ensure you are using standard Excel time codes like "h:mm AM/PM" or "hh:mm:ss". If you use incorrect letters or forget the quotation marks around the format string, the formula will return an error or display literal letters.