Function Problems

How to Extract Time from Excel Cells and Display It in 24-Hour Format in WPS Office

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 869 views

Question details

The user needs to extract only the time portion from date-time values in column D, including values that use a non-breaking space between the date and time, and display the result in 24-hour hh:mm:ss format.

How to Extract Time from Excel Cells and Display It in 24-Hour Format
Product
WPS Office Excel
Device & OS
not provided
Scenario
The user has a worksheet with date-time values in column D and needs a clean time column for reporting or calculation.
Observed behavior
Date-time values may use a regular or non-breaking space separator; direct time extraction can return text or fail to evaluate as a numeric time.
Before you start

Open the WPS Office Excel workbook and confirm the date-time values are in column D. If the separator between the date and time is a non-breaking space, use CHAR(160) in the formula rather than a normal space.

Solution 1Recommended

Use SUBSTITUTE with CHAR(160), TIMEVALUE, and TEXT to Extract and Format Time

Recommended when you need a formula that reliably handles both normal and non-breaking spaces and returns a true time value formatted as hh:mm:ss.

This method first replaces any non-breaking space with a normal space, then converts the time text into a numeric time value. Formatting the result with TEXT displays it in 24-hour hh:mm:ss format.

1
Select the target cell

Click the cell where you want the extracted time, for example E2.

2
Enter the extraction formula

Type =TEXT(TIMEVALUE(SUBSTITUTE(D2,CHAR(160)," ")),"hh:mm:ss") and press Enter. This replaces non-breaking spaces, converts the time text into a numeric time value, and formats it as 24-hour time.

3
Fill the formula down

Drag the fill handle from the corner of E2 down through the remaining rows in column D to apply the formula to all date-time cells.

Use SUBSTITUTE with CHAR(160), TIMEVALUE, and TEXT to Extract and Format Time
Result: The cell now shows only the time in hh:mm:ss format, even when the original date and time are separated by a non-breaking space.
Use WPS Office Excel

Extract Time in WPS Office Excel with a Reliable Formula

WPS Office Excel supports the same SUBSTITUTE, TIMEVALUE, and TEXT functions, so you can extract time from date-time cells and display it in 24-hour format without switching tools.

  1. 1. Open your workbook in WPS Office Excel: Launch WPS Office Excel and open the file that contains date-time values in column D.
  2. 2. Enter the time extraction formula: Click the first target cell, enter =TEXT(TIMEVALUE(SUBSTITUTE(D2,CHAR(160)," ")),"hh:mm:ss"), and press Enter.
  3. 3. Fill the formula down: Drag the fill handle down column E to apply the formula to all date-time rows.
  4. 4. Verify the 24-hour time format: Check that the extracted values appear as hh:mm:ss and adjust column width or formatting if needed.
Handles non-breaking spaces with CHAR(160) and SUBSTITUTEReturns a true numeric time value for further calculationsFull compatibility with Microsoft Excel .xlsx formulas and formattingFree and lightweight alternative to Microsoft Office Excel
microsoft office alternative - wps office

Frequently Asked Questions

Why does my time extraction return #VALUE! in WPS Excel?

This often happens when the date and time are separated by a non-breaking space. Wrap the reference with SUBSTITUTE(D2,CHAR(160)," ") before using TIMEVALUE, for example =TEXT(TIMEVALUE(SUBSTITUTE(D2,CHAR(160)," ")),"hh:mm:ss").

Can I display the extracted time without the date?

Yes. Use TEXT(...,"hh:mm:ss") to show only the time. Use hh:mm for hours and minutes, or hh:mm:ss for hours, minutes, and seconds.

How do I convert the result into a real time value for calculations?

Use TIMEVALUE on the cleaned text. In the recommended formula, TIMEVALUE(SUBSTITUTE(D2,CHAR(160)," ")) creates a numeric time that can be added, subtracted, or averaged.

Does this formula work in Microsoft Excel as well?

Yes. The functions SUBSTITUTE, CHAR, TIMEVALUE, and TEXT are compatible with Microsoft Excel, so the same formula can be used in both WPS Office Excel and Microsoft Excel.

Can I use hh:mm instead of hh:mm:ss?

Yes. Replace "hh:mm:ss" with "hh:mm" in the TEXT format argument to display a shorter 24-hour time.