Convert Text Values to Minutes, Seconds, and Milliseconds in WPS Spreadsheets
Question details
The user needs to convert numeric values such as 1234.789 into a time duration display showing minutes, seconds, and milliseconds in a spreadsheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A column contains raw values like 1234.789 that represent minutes, seconds, and milliseconds, and the user wants them displayed as [m]:ss.000.
- Observed behavior
- The raw values appear as general numbers or text, and changing only the cell format does not correctly produce the minutes, seconds, and milliseconds time display.
Make sure your source values are in a single column, for example A2:A100, and identify the first empty column where the converted time values can be placed. Keep the original column available because the formula references the raw values.
Use a formula and the [m]:ss.000 custom number format
Use this method when values such as 1234.789 must be converted into a real time value that displays minutes, seconds, and milliseconds.
The formula divides the value into two parts: the minute portion is converted to a fraction of a day by dividing by 1440, and the remaining seconds/milliseconds portion is converted by dividing by 86400. The custom number format [m]:ss.000 then displays the resulting serial time with elapsed minutes, seconds, and milliseconds.
Click cell B2, type =QUOTIENT(A2,100)/1440+MOD(A2,100)/86400, and press Enter.
With B2 selected, press Ctrl+1, click Custom under the Number tab, enter [m]:ss.000 in the Type box, and click OK.
Drag the fill handle at the lower-right corner of B2 down through the remaining rows to apply the formula and number format.
![Use a formula and the [m]:ss.000 custom number format](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-13/596f3b7eb82f09f4b1dd8571e926431a.png)
Convert Text Values to Minutes, Seconds, and Milliseconds in WPS Spreadsheets
WPS Spreadsheets supports the same conversion formula and custom number format as Excel, so you can turn raw values such as 1234.789 into 12:34.789 while keeping full .xlsx workbook compatibility.
- 1. Download WPS Office: Go to the official WPS Office website and download the free installer for your device.
- 2. Open the workbook in WPS Spreadsheets: Click the WPS Office Spreadsheets module and open the workbook that contains the raw values.
- 3. Enter the conversion formula: Select the first result cell, type =QUOTIENT(A2,100)/1440+MOD(A2,100)/86400, and press Enter.
- 4. Apply the custom format: Press Ctrl+1, choose Custom, enter [m]:ss.000, and click OK.
- 5. Fill down and save: Drag the fill handle down to convert the remaining values, then save the workbook as an .xlsx file.
Frequently Asked Questions
Why can’t I just apply a time format to the raw values?
Time formats only change how a serial date/time value is displayed. A raw value such as 1234.789 is not an Excel serial time, so you must first convert it with the formula and then apply the [m]:ss.000 custom format.
What does the formula =QUOTIENT(A2,100)/1440+MOD(A2,100)/86400 do?
QUOTIENT(A2,100) takes the minutes part and divides by 1440 to convert minutes to a fraction of a day. MOD(A2,100) takes the seconds/milliseconds part and divides by 86400 to convert seconds to a fraction of a day. Together they create a real time serial value.
Can I use this method in WPS Spreadsheets with a file created in Excel 2019?
Yes. WPS Spreadsheets supports the same formulas and custom number formats and can open, edit, and save .xlsx files without changing the workbook structure.
Will the converted values work in time calculations?
Yes. Because the formula returns a real time serial value, the converted cells can be added, subtracted, and used with time functions in both WPS Spreadsheets and Excel.
