Fix Excel TIMEVALUE Errors for 0000–0059 Times
Question details
The user needs to convert four-digit values representing times from 0000 through 0059 into valid time values without triggering a TIMEVALUE error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains values such as 0030, and the user wants to interpret them as times such as 00:30.
- Observed behavior
- TIMEVALUE returns a #VALUE! error because Excel may not recognize a leading-zero four-digit value as a valid time string.
Confirm that the source value is in cell A1 or identify the cell containing the four-digit time. The formula expects values such as 30 or 0030 and will normalize them to a four-character string before inserting the time separator.
Convert 0000–0059 values into valid times with a formula
Use this method when TIMEVALUE fails on four-digit values with leading zeros, such as 0030 or 0059.
The formula first formats the source as four digits, inserts a colon before the final two digits, and coerces the resulting text into a spreadsheet time serial value. For example, 0030 becomes 00:30.
Select an empty result cell, click the formula bar, and enter =--REPLACE(TEXT(A1,"0000"),3,0,":") where A1 contains the four-digit value.
Press Enter to calculate the result. A value such as 0030 is converted to the time value 00:30.
Select the result cell, open the Home tab, choose the Number Format menu, and select Time or a custom format such as hh:mm.
Convert leading-zero time values in WPS Spreadsheets
WPS Spreadsheets supports the same TEXT, REPLACE, and unary conversion approach, allowing you to turn values such as 0030 into usable time values and save the corrected workbook in a Microsoft Excel-compatible format.
- 1. Open the workbook: Launch WPS Office, open WPS Spreadsheets, and load the workbook containing the four-digit time values.
- 2. Enter the formula: Click an empty result cell and enter =--REPLACE(TEXT(A1,"0000"),3,0,":"), replacing A1 with the source cell if necessary.
- 3. Format the result: On the Home tab, open the Number Format menu and choose Time or apply a custom hh:mm format.
- 4. Save the corrected file: Choose Menu > Save As, select Excel Workbook (*.xlsx) if needed, and click Save.
Frequently Asked Questions
Why does TIMEVALUE return #VALUE! for 0030?
Excel may treat 0030 as a four-digit number or text string rather than a recognizable time. Inserting a colon to create 00:30 before coercing the value resolves the issue.
What does the formula =--REPLACE(TEXT(A1,"0000"),3,0,":") do?
TEXT formats the value as four digits, REPLACE inserts a colon at the third character, and the double unary operator converts the resulting text, such as 00:30, into a numeric time value.
Can this formula convert 0059 correctly?
Yes. If A1 contains 0059, the formula creates 00:59 and converts it to a valid time value. Format the result as hh:mm if it displays as a decimal.
What should I do if the result shows a decimal instead of a time?
Select the formula result, open the Home tab, choose the Number Format menu, and select Time or a custom hh:mm format. Spreadsheet times are stored internally as fractions of a day.
