Convert Six-Digit Numbers to Excel Time Values in WPS Office Excel
Question details
The user needs to convert imported six-digit values such as 151521 into displayed Excel time values such as 15:15:21.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user imported or entered a six-digit value and wants it displayed as a time value in Excel.
- Observed behavior
- Applying a time format alone returns 00:00:00 because Excel treats the whole number as days, not as an HHMMSS time code.
Open the worksheet that contains the imported six-digit values and identify the first cell, for example A1. Check that the values are numeric and contain no separators or leading characters.
Create a real Excel time value with TEXT and TIMEVALUE
Use this method when you need the converted value to behave as an actual time for sorting, summing, or other calculations.
The TEXT function reformats the six-digit number as 00:00:00 text, and TIMEVALUE converts that text into a true Excel time serial number.
Click an empty cell next to the six-digit number, such as B1 if the value is in A1.
Type =TIMEVALUE(TEXT(A1,"00\:00\:00")) and press Enter. The formula returns a value that WPS Office Excel can display as 15:15:21.
Select the result cell, press Ctrl+1, click Time in the Format Cells dialog, choose 13:30:55 or hh:mm:ss, and click OK.
Return the time as text only
Use this method when you only need the display value 15:15:21 and do not plan to calculate with the result.
Convert Six-Digit Time Values with WPS Office Excel
WPS Office Excel can convert imported six-digit codes such as 151521 into proper time values like 15:15:21 using familiar Excel formulas and time formatting, without changing your workflow.
- 1. Open the worksheet in WPS Office Excel: Launch WPS Office Excel and open the spreadsheet that contains the six-digit time values.
- 2. Enter the conversion formula in a helper column: In an empty cell, type =TIMEVALUE(TEXT(A1,"00\:00\:00")) and press Enter.
- 3. Format the result as Time: Select the result cell, press Ctrl+1, click Time, choose 13:30:55 or hh:mm:ss, and click OK.
- 4. Apply the conversion to the remaining rows: Drag the fill handle down the helper column to convert other six-digit values.
Frequently Asked Questions
Why does 151521 show 00:00:00 when I apply a time format?
Excel stores dates and times as serial numbers where whole numbers are days. The value 151521 has no fractional-day component, so a time format shows midnight. Use =TIMEVALUE(TEXT(A1,"00\:00\:00")) to parse it as 15:15:21.
Can I display the result as text instead of a numeric time value?
Yes. Enter =TEXT(A1,"00\:00\:00") in a blank cell. This returns the text string 15:15:21 and is useful when you only need the visual format.
How do I apply hh:mm:ss formatting after using TIMEVALUE?
Select the result cell, press Ctrl+1 to open Format Cells, click Time, choose 13:30:55 or type hh:mm:ss under Custom, and click OK.
Can I convert a whole column of six-digit time values at once?
Yes. Enter the formula in the first row of a helper column, then double-click the fill handle or drag it down. The cell references update automatically for each row.
Does this formula work in both WPS Office Excel and Microsoft Excel?
Yes. TEXT and TIMEVALUE are standard Excel functions, so the same formulas work in WPS Office Excel and Microsoft Excel, and the file remains .xlsx compatible.
