Power Query Problems

How to Sum Text-Formatted Durations in Excel using Power Query

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to sum duration data exported from an ERP system that is formatted as text strings without manually editing or deleting digits.

Product: Microsoft Excel (Office LTSC Professional Plus 2021)
Device & OS: not provided
Scenario: Importing and analyzing ERP system exports containing time or duration data formatted as non-calculable text.
Observed behavior: Excel treats the imported duration formats (e.g., 00001:35:43) as text, preventing standard SUM formulas from calculating the total time accurately.

Before you start

Verify that your ERP system allows you to export your report as a structured CSV file, which will optimize the data import and transformation process.

Solution 1Recommended

Use Power Query to Convert and Calculate Text Durations

Power Query can automatically convert text strings into standard Excel time or duration formats during the import process, eliminating manual data cleaning.

Instead of opening the ERP file directly in Excel, importing it through Power Query allows you to establish automated transformation rules. This ensures that text-based durations are correctly recognized as time values.

Import the CSV File via Power Query

Open a blank Excel workbook. Navigate to the Data tab on the ribbon, click on Get Data, choose From File, and select From Text/CSV to locate your exported ERP file.

Open the Power Query Editor

Select your CSV file and click the Transform Data button. This will launch the Power Query Editor with a preview of your data.

Convert the Text Column to Duration

Locate the column containing your text-formatted durations. Right-click the column header, navigate to Change Type, and select Duration or Time. This converts the text strings into valid numerical formats.

Load the Data into Excel

Click the Close & Load button in the top left corner. The cleaned and converted data will be inserted into your worksheet as a formatted table.

Sum the Durations and Automate Future Updates

Use the standard =SUM() formula at the bottom of your duration column. Whenever your ERP source file is updated, simply replace the original CSV file in its folder, open your Excel workbook, and click Data > Refresh All to update the sums automatically.

Custom Time Formatting: If your total sum exceeds 24 hours, right-click the total cell, select Format Cells, choose Custom, and type [h]:mm:ss to prevent the hours from rolling over.

Data Processing with WPS Office

Easily Import and Calculate Time Durations in WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive data import tools, allowing you to easily handle text-based time exports from external systems and calculate sums without tedious manual editing.

1. Import Your ERP Data: Open WPS Spreadsheet, go to the Data tab, and click Import Data to select your exported CSV or text file.
2. Format Data During Import: Follow the Text Import Wizard prompts. Ensure your duration column is selected and correctly parsed.
3. Use Text to Columns if Necessary: If the data requires further cleaning, highlight the column, select Text to Columns under the Data tab, and trim any excess characters.
4. Calculate the Total: Select the cell beneath your formatted durations and use the AutoSum button or the =SUM() formula to instantly calculate the total time.

Free to use with a lightweight, fast installationFully compatible with Microsoft Excel (.xlsx) formats and formulasAdvanced text-to-columns and CSV import wizard for easy data cleaningFamiliar ribbon interface ensuring a seamless transition

Frequently Asked Questions

Why doesn't the standard SUM function work on my exported durations?

When ERP systems export reports, durations are often formatted as text strings (e.g., '00001:35:43') rather than valid time values. Excel's SUM function only works on numerical data and will ignore any cells containing text.

Can I use the TIMEVALUE function to convert these text strings without Power Query?

Yes, you can use the =TIMEVALUE(A2) formula in an adjacent column to convert standard text representations of time into an Excel serial number. However, if the format contains extra leading zeros (like 00001:35:43), you may need to combine it with functions like RIGHT or MID to extract the correct time format first.

How do I display total durations that exceed 24 hours in Excel?

By default, Excel resets time formatting after 24 hours. To display cumulative time, apply a custom number format. Right-click the cell with your sum, select Format Cells, choose the Custom category, and enter [h]:mm:ss in the Type field.

Excel & Data Problems