How to Create a Custom Excel Format for Hours and Minutes
Question details
The user wants to format decimal-style time values (like 48.37) into a specific hours and minutes format (like 48h 37m) and apply this custom format across multiple rows.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Organizing or calculating time logs where durations are inputted as decimals but need to be displayed as exact elapsed hours and minutes.
- Observed behavior
- The current data displays as standard decimals (48.37) instead of the desired time layout (48h 37m), requiring a batch-applicable custom cell format.
Ensure your time data is entered consistently. If your values are currently plain text or standard decimals rather than true time values, you must convert them into a valid time format before applying the custom display layout.
Apply a Custom Time Format to Multiple Cells
Use the 'Format Cells' feature to create a reusable custom display layout for true time values, showing them as hours and minutes across your selected rows.
By defining a custom number format, you change how the data looks without altering the underlying value, ensuring calculations still work correctly.
Highlight all the rows, columns, or specific cells containing the time values you want to format.
Right-click the highlighted area and choose 'Format Cells' from the context menu, or press the Ctrl+1 shortcut on your keyboard.
In the Format Cells dialog box, go to the 'Number' tab and select 'Custom' from the Category list on the left side.
In the 'Type' input field, delete the default text and enter the exact custom format code: [h]"h "mm"m"
Click 'OK' to apply the new format. Your values will now display as hours and minutes. This format is saved in the Custom list for the current workbook, so you can easily reuse it for other cells later.
Convert Decimal Text Values to True Time
If your values are treated as standard decimals (e.g., 48.37) instead of true time durations, use a formula to convert them before applying your custom format.
Quickly Format Time Data with WPS Spreadsheet
WPS Spreadsheet makes complex data formatting effortless. You can quickly define, batch-apply, and reuse custom time formats across hundreds of rows in seconds.
- 1. Open Data: Launch WPS Spreadsheet and open your workbook containing the time logs.
- 2. Select Range: Highlight the columns or specific cells you need to adjust.
- 3. Access Custom Formatting: Press Ctrl+1 to instantly open the Format Cells window.
- 4. Apply Code: Click 'Custom', type your hours and minutes format code, and hit OK.
Frequently Asked Questions
Why does my formatted time reset to zero after 24 hours?
If you use the standard 'h' format, the system assumes it is a clock time and resets every 24 hours. To display cumulative elapsed hours beyond 24, you must wrap the 'h' in square brackets, writing it as '[h]' in your custom format code.
How do I apply my new custom format to other sheets?
Once you create a custom format in a workbook, it is automatically saved at the bottom of the 'Custom' category list. You can simply select the new cells, open the Format Cells dialog, and select it from the list without typing it out again.
Can I calculate the total of these formatted time cells?
Yes. Custom formatting only changes how the data is visually displayed. The underlying numeric values remain perfectly intact, meaning you can still use the SUM function and other formulas normally.
