Formatting Issues

How to Restore a Custom Time Entry Format in Excel

Maira Mehtab Sep 27, 2026 870 views

Question details

The user wants to format standard number entries (such as 1158 or 12.34) so they display as a time (11:58), or convert them into true time values for use in formulas.

Product: Excel
Device & OS: not provided
Scenario: Streamlining data entry by typing raw numbers instead of colons, then formatting or converting those numbers to represent time accurately.
Observed behavior: When applying standard number formatting to make numbers look like time, the underlying cell value remains a raw number, preventing accurate time-based calculations.

Before you start

Determine whether you only need the numbers to look like time for display purposes, or if you need to perform calculations on them, as this determines the method you should apply.

Solution 1Recommended

Apply a Custom Number Format for Display Only

Use this method if you only want to quickly change how raw numbers appear without performing time-based calculations on them.

By applying a specific custom format, entering a number like 123 will display as 01:23, and 1158 will display as 11:58. Keep in mind that the underlying value remains a number, meaning time calculations will not work on these cells.

Select the target cells

Highlight the cells or column where you want to enter the numbers.

Open the Format Cells dialog

Right-click the selected cells and choose 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.

Enter the custom format code

Navigate to the 'Number' tab, select 'Custom' from the Category list, and type 00\:00 in the Type box.

Apply the format

Click 'OK' to save. Any 3- or 4-digit number you type into these cells will now display with a colon.

Calculation Limitation: Because the cell retains the raw number value, subtracting or adding these pseudo-time values will result in standard decimal math rather than accurate time math.

Solution 2

Convert Decimal Numbers to True Time Values Using Formulas

Use this method to convert decimal-style entries (like 12.34) into real Excel time values, enabling precise time math.

Advanced Formatting in WPS Office

Format and Calculate Time Effortlessly in WPS Spreadsheet

WPS Spreadsheet offers seamless support for advanced custom number formatting and time conversion formulas. You can easily replicate Excel's time formatting tricks and DOLLARDE function within a highly compatible, easy-to-use interface.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open the workbook containing the number entries you wish to format.
2. Highlight and open formatting options: Select the cells you want to modify, right-click, and select 'Format Cells'.
3. Apply Custom formatting: Go to the Custom category and enter 00\:00 to format numbers as time for display purposes.
4. Calculate true time: Alternatively, utilize the exact same =DOLLARDE() formula in WPS Spreadsheet to convert decimals into calculation-ready time values.

Fully compatible with Microsoft Excel formulas and custom number formatsLightweight application that runs smoothly across multiple operating systemsIntuitive 'Format Cells' interface for rapid custom formattingFree to use with comprehensive built-in function libraries

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my custom time format working in spreadsheet calculations?

Custom number formats only change how the data is visually presented, not the underlying data. A custom format like 00\:00 applied to the number 1158 simply inserts a colon for display, keeping the value as 1158 rather than a true time serial number.

How can I enter a time quickly without typing a colon?

You can type the numbers directly (e.g., 123 for 1:23) and apply the custom format 00\:00 to the cells. The colon is added automatically for you, making data entry much faster.

What does the DOLLARDE function do when converting time?

The DOLLARDE function is typically used to convert fractional dollar prices to decimal numbers. When applied to time conversion, dividing the result of DOLLARDE by 24 accurately translates a decimal time representation (like 12.34) into a recognized time serial number.

Document & File Issues