Calculation Issues

How to Convert Decimal-Looking Production Times to Valid Excel Time

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate the sum of production times entered as decimals (e.g., 1.48, 3.15) and display the total accurately as standard hours and minutes.

Product: Excel
Device & OS: not provided
Scenario: Calculating total production time from raw inputs that incorrectly use decimal points instead of colons to separate hours and minutes.
Observed behavior: Time values like 1.48 and 13.91 are being treated as standard decimal numbers rather than valid hours and minutes, resulting in incorrect calculations when summed.

Before you start

Ensure that the decimal part of your production numbers does not exceed 59 (e.g., 13.91 is invalid), as true time formatting requires minute values to be between 00 and 59.

Solution 1Recommended

Use Find and Replace to Change Decimals to Colons

The most straightforward method is to physically replace the decimal points with colons so Excel immediately recognizes the data as valid time.

Excel calculates time as a fraction of a 24-hour day. Entering 1.48 tells Excel you mean 1 and 48/100 of a day, not 1 hour and 48 minutes. By swapping the decimal point for a colon, the entries become recognizable time values that can be properly calculated.

When summing time that might exceed 24 hours, you must use a specific custom format to prevent the clock from resetting back to zero.

Select the target cells

Highlight the range of cells containing the decimal-looking production times.

Open Find and Replace

Press Ctrl + H on your keyboard to open the Find and Replace dialog box.

Replace periods with colons

In the 'Find what' box, type a period (.). In the 'Replace with' box, type a colon (:). Click 'Replace All' to convert the numbers into valid time formats.

Sum the time values

Select an empty cell below your data and use the standard formula =SUM(A1:A10) (adjusting the range as needed) to total the hours.

Apply cumulative time formatting

Right-click the cell containing the total and select 'Format Cells'. Go to the 'Custom' category, type [h]:mm into the Type field, and click OK. This ensures totals over 24 hours display correctly.

Data Validation Warning: Double-check values like 13.91 before converting. 0.91 hours is not 91 minutes, and a clock format will not accept 91 as a valid minute entry.

Solution 2

Convert Using the TIME Function

If you cannot modify the original dataset, use a formula in a helper column to parse the decimal numbers into standard hours and minutes.

Efficient Time Management

Easily Format and Sum Time Values in WPS Spreadsheet

WPS Spreadsheet provides powerful custom formatting and formula tools to handle complex time calculations, ensuring your production hours are always accurate without the hassle.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your production times.
2. Replace decimals: Highlight your data, press Ctrl + H, and replace all periods (.) with colons (:) to create valid time entries.
3. Total the values: Use the =SUM() function to add the time cells together.
4. Format correctly: Right-click the total, select 'Format Cells', go to 'Custom', and input [h]:mm to properly display total hours.

Fully compatible with Microsoft Excel's time formats, formulas, and functions.Quickly apply the [h]:mm custom format for tracking cumulative hours exceeding 24.Lightweight, fast, and free to use for everyday data processing and reporting.

Frequently Asked Questions

Why does my total time reset after 24 hours?

By default, spreadsheet programs display time on a 24-hour rolling clock. To show cumulative or elapsed time beyond 24 hours, you must use the custom number format [h]:mm instead of the standard h:mm.

How do I calculate payroll by multiplying time by an hourly rate?

Because time is stored as a fraction of a 24-hour day, you cannot multiply a time format directly by an hourly rate. First, multiply the valid time cell by 24, and then multiply that result by your hourly rate.

Why does typing 0.5 display as 12:00 PM?

Spreadsheet software considers the number 1 as a full 24-hour day. Therefore, 0.5 represents half a day, which corresponds precisely to 12:00 PM (noon).

How do I fix entries where the decimal exceeds .59?

Entries like 13.91 cannot be directly converted to 13 hours and 91 minutes because an hour only has 60 minutes. You must review the raw data source to determine if 13.91 meant decimal hours (13 hours and 54.6 minutes) or if it was a data entry error.

Excel & Data Problems