Function Problems

How to Convert Text to Cumulative Hours Over 24 Hours in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to convert a text-formatted duration exceeding 24 hours (e.g., '28:15') into a functional, cumulative numeric time value in Excel.

Product: Excel
Device & OS: not provided
Scenario: Working with exported time data exceeding 24 hours that Excel recognizes as text instead of numeric durations.
Observed behavior: Using the TIMEVALUE function fails for durations longer than 24 hours, and the data remains as text, preventing further mathematical calculations.

Before you start

Identify the cell containing your text-formatted time data and ensure that there are no hidden spaces or non-breaking characters causing errors.

Solution 1Recommended

Convert Text to Number Using Simple Math Operations

Applying a basic math operation like adding zero or multiplying by one forces Excel to evaluate the text as a numeric time value.

When Excel imports or exports data, time durations over 24 hours are often formatted as text. The TIMEVALUE function will not work for times beyond 24 hours, but simple arithmetic forces a data type conversion.

Select a blank cell

Click on an empty cell adjacent to your text-formatted time data (e.g., E2 if your data is in D2).

Enter the conversion formula

Type =D2+0 or =D2*1 into the formula bar and press Enter. This operation converts the text string into a numeric value.

Apply cumulative time formatting

Right-click the result cell and select 'Format Cells'. Go to the 'Custom' category and type [h]:mm or [hh]:mm in the Type box. Click OK to display the cumulative hours correctly.

Calculation Ready: Once formatted as [h]:mm, you can seamlessly add or subtract these cumulative hours in other formulas.

Manage Time Formats Easily

Easily Handle Cumulative Time Formats in WPS Spreadsheet

WPS Spreadsheet provides robust support for custom cell formatting and formula operations, making it incredibly simple to handle exported time data.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your exported spreadsheet containing the text time values.
2. Apply the math operation: In an adjacent cell, enter the formula =D2+0 to force the text into a numerical value.
3. Apply custom formatting: Press Ctrl+1 to open Format Cells, select Custom, and enter [h]:mm to display the hours correctly.

100% compatible with Microsoft Excel formulas and custom formats.Easily apply [h]:mm formatting to track cumulative durations.Lightweight software that loads large datasets instantly.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the TIMEVALUE function work for 28:15?

The TIMEVALUE function is designed to convert text representations of standard clock times (between 0:00:00 and 23:59:59) into serial numbers. It cannot process durations or cumulative hours that exceed 24 hours.

What does the bracketed [h] mean in custom formatting?

Placing brackets around the 'h' (i.e., [h]:mm) tells the spreadsheet program to display elapsed or cumulative time rather than standard clock time, preventing the hours from resetting to zero after 24 hours.

Can I convert an entire column of text times at once?

Yes. You can write the =D2+0 formula in the top row of an adjacent column, drag the fill handle down to apply it to all rows, and then apply the [h]:mm custom format to the entire new column.

Why did my converted time turn into a regular decimal number like 1.177?

Dates and times are stored as serial numbers (where 1 equals 24 hours). A value like 1.177 represents the underlying mathematical value. You simply need to change the cell format to Custom [h]:mm to display it as 28:15.

Excel & Data Problems