How to Extract Time from a Date-Time Value in WPS Spreadsheets Without TIMEVALUE
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.

- 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.
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.
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.
Click the cell where you want to show the time, for example B1.
Type =MOD(A1,1) and press Enter. Replace A1 with your date-time cell reference.
With the result cell selected, go to Home > Number Format, choose Time or More Number Formats, and select hh:mm:ss.

Use the INT Function as an Alternative
Useful if you prefer subtracting the integer date portion from the original date-time value instead of using MOD.
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. Open your workbook in WPS Spreadsheets: Open the workbook that contains the numeric Excel date-time values in WPS Office.
- 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. Format the result as Time: Go to Home > Number Format, choose Time or hh:mm:ss to display the extracted time.
- 4. Confirm the result: Check that the cell now shows only the time portion with the correct hours, minutes, and seconds.

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.




