Function Problems

How to Extract Time from a Date-Time Value in WPS Spreadsheets Without TIMEVALUE

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 869 views

Question details

The user needs to extract the time portion from a cell that already contains a numeric Excel date-time value without using TIMEVALUE, which is designed for text.

How to Extract Time from a Date-Time Value in WPS Spreadsheets Without TIMEVALUE
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
They have a date-time serial number in a worksheet cell, such as A1, and want to display only the time in another cell.
Observed behavior
TIMEVALUE returns an error or incorrect result because the source value is not a text string.
Before you start

Confirm the source cell contains a numeric date-time serial number rather than text, and pre-format the target cell as Time (hh:mm:ss) before or after entering the formula.

Solution 1Recommended

Use the MOD Function to Isolate the Time Fraction

Recommended when the date-time is already a numeric Excel or WPS value; MOD removes the day integer and leaves the fractional time.

In Excel and WPS Spreadsheets, dates are stored as whole numbers and times as decimal fractions. The MOD function returns the remainder after dividing by 1, effectively stripping the date portion.

1
Select the result cell

Click the cell where you want to show the time, for example B1.

2
Enter the MOD formula

Type =MOD(A1,1) and press Enter. Replace A1 with your date-time cell reference.

3
Apply a time format

With the result cell selected, go to Home > Number Format, choose Time or More Number Formats, and select hh:mm:ss.

Use the MOD Function to Isolate the Time Fraction
Time extracted successfully: The cell now displays only the time based on the fractional part of the date-time serial number.
WPS Spreadsheets formula

Extract Time from a Date-Time Value in WPS Spreadsheets

WPS Spreadsheets supports Excel-compatible functions, so you can enter =MOD(A1,1) and format the result as Time to extract the time from a numeric date-time value. This works without TIMEVALUE and maintains compatibility with your existing Excel files.

  1. 1. Open your workbook in WPS Spreadsheets: Open the workbook that contains the numeric Excel date-time values in WPS Office.
  2. 2. Select the result cell and enter the formula: Click the cell for the time result and type =MOD(A1,1), replacing A1 with the source cell.
  3. 3. Format the result as Time: Go to Home > Number Format, choose Time or hh:mm:ss to display the extracted time.
  4. 4. Confirm the result: Check that the cell now shows only the time portion with the correct hours, minutes, and seconds.
Extracts time with the same =MOD(A1,1) formula used in ExcelFull compatibility with .xlsx and .xls date-time serial valuesFree and lightweight spreadsheet tool with a familiar interfacePreserves time formatting and calculation behavior across WPS and Excel
microsoft office alternative - wps office

Frequently Asked Questions

Why does TIMEVALUE fail when my cell already shows a date and time?

TIMEVALUE expects text. In WPS Spreadsheets, if the cell contains a numeric date-time serial number, use =MOD(A1,1) and format the result as Time instead.

Can I extract only the hour or minute instead of the full time?

Yes. Use =HOUR(A1), =MINUTE(A1), or =SECOND(A1) on the source cell, or apply a custom time format to the =MOD(A1,1) result.

Does =MOD(A1,1) work in Microsoft Excel and WPS Spreadsheets?

Yes, both use the same serial date-time system, so the formula and Time formatting work in both applications.

What should I do if my date-time value is stored as text?

If the value is text, TIMEVALUE may work for time-only text. For text date-times, convert them to real date-time serial numbers first, then use =MOD(A1,1).

How do I apply hh:mm:ss formatting to the formula result?

Select the cell, go to Home > Number Format > Time, or right-click > Format Cells > Time, and choose hh:mm:ss.