How to Preserve Time Formatting with CONCATENATE and TEXT in Excel
Question details
The user wants to combine a time value with a text string in a single cell without losing the readable time format.

- 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.
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.
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.
Click on the empty cell where you want the combined text and time to be displayed.
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.
Add TEXT(P10, "h:mm:ss AM/PM") to the formula, replacing 'P10' with the cell reference that contains your time value.
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.

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. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your text and time data.
- 2. Select the target cell: Click the blank cell where the combined result should appear.
- 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. Apply the calculation: Press Enter to instantly display the perfectly formatted text string.

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.




