How to Automatically Format Numbers as Time (012345 to 01:23:45) in Excel
Question details
The user wants to automatically display a six-digit continuous number (e.g., 012345) as a standard time format containing hours, minutes, and seconds (01:23:45) without manually typing the colons.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing repetitive data entry for runtimes, logs, or timestamps where typing colons between every two digits is tedious and time-consuming.
- Observed behavior
- By default, entering a sequence like 012345 displays as the whole integer 12345, ignoring the leading zero and lacking the colons required to visually interpret the data as time.
Determine whether you only need the numbers to visually look like time, or if you need them converted to actual time values for calculations. Visual formatting is faster, but formulas are required if you need to add or subtract the durations.
Apply a Custom Number Format (For Display Only)
The fastest method to display a six-digit integer with colons using Excel's cell formatting without altering the underlying data.
This method changes how the number looks but keeps its actual integer value. Because the cell still holds a whole number (e.g., 12345 instead of an actual fractional time value), it is not suitable for time-based calculations.
Highlight the column or cells containing the six-digit runtime numbers.
Right-click the selected cells and choose 'Format Cells', or press 'Ctrl + 1' on your keyboard.
Navigate to the 'Number' tab and select 'Custom' from the Category list on the left.
In the 'Type' text box, enter exactly '00\:00\:00' (without quotes) and click 'OK'.
Use Helper Columns and the TIME Function (For True Time Calculations)
Convert raw six-digit numbers into true Excel time values using a formula, enabling you to perform time-based calculations.
Easily Format and Calculate Time Data with WPS Spreadsheet
WPS Spreadsheet offers powerful, fully compatible formatting tools. Whether you need custom number formats for fast data entry display or advanced functions to calculate true time values, WPS Office handles it seamlessly.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the raw numbers.
- 2. Highlight your data: Select the column or range of cells with the continuous numbers.
- 3. Apply Custom Format: Right-click, select 'Format Cells', navigate to 'Custom', and input the code 00\:00\:00 to format the display instantly.
- 4. Use Formulas for Calculations: Alternatively, utilize the fully supported =TIME() function to convert those raw numbers into calculation-ready real time values.
Frequently Asked Questions
Why doesn't the custom format 00:00:00 work without the backslashes?
In Excel, a colon is a reserved mathematical time separator. To force Excel to display a colon as regular text within a custom number format applied to an integer, you must escape it by placing a backslash (\) immediately before the colon.
Will this custom format work for numbers with fewer than six digits?
Yes. By using the custom format code 00\:00\:00, Excel automatically pads the number with leading zeros. For example, entering 123 will be treated as 000123 and will visually display as 00:01:23.
Can I format 4-digit numbers as minutes and seconds (MM:SS)?
Yes. If your data entries represent minutes and seconds without hours (e.g., typing 1234 to represent 12:34), you can apply the custom number format 00\:00 instead.
How do I convert the visually formatted numbers into real time values later?
If you initially applied a custom format, the underlying value remained an integer. To convert it into true Excel time later, you will need to create a helper column and use a formula combining the TIME, LEFT, MID, and RIGHT functions to parse the hours, minutes, and seconds.
