Calculation Issues

How to Format Durations and Calculate Time in Excel

Phi Hung Vo Oct 15, 2026 871 views

Question details

The user needs to properly format recording durations in Excel to display minutes and seconds, and calculate averages, totals, and cost per minute based on those durations.

How to Format Durations and Calculate Time Averages in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking time durations for recordings or tasks and performing mathematical operations (sum, average, cost analysis) on the accumulated time.
Observed behavior: Time formatting fails and displays single digits (like 0 or 3) instead of full durations because a semicolon was mistakenly used instead of a colon in the custom format code.

Before you start

Ensure you enter your time values using a standard time format (e.g., type 0:03:58 for 3 minutes and 58 seconds) so Excel recognizes the cell contents as a time value rather than plain text.

Solution 1Recommended

Apply the Correct Custom Duration Format

Use Excel's custom formatting rules to accurately display elapsed time in minutes and seconds without it rolling over into hours.

By default, Excel formats time as a time of day. To track cumulative or elapsed time (where minutes can exceed 60), you must wrap the time unit in square brackets.

Select the duration cells

Highlight the cells containing your entered time values (e.g., 0:03:58).

Open 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

Go to the 'Number' tab, select 'Custom' from the category list. In the 'Type' field, enter [m]:ss to display total minutes and seconds. Make sure to use a colon (:), not a semicolon (;).

Apply the Correct Custom Duration Format

Tip: If you are tracking longer durations, use [h]:mm:ss to prevent hours from resetting to zero after 24 hours.

Solution 2

Calculate Time Averages, Totals, and Cost per Minute

Use standard Excel formulas to sum or average your time values, and apply a mathematical conversion to calculate monetary costs based on duration.

Format and Calculate Durations Easily with WPS Office

WPS Spreadsheet offers robust, built-in support for complex time tracking. You can seamlessly apply custom time formats like [m]:ss and execute advanced cost calculations without compatibility issues.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your time-tracking data.
2. Select your data: Highlight the cells with your time durations, right-click, and select 'Format Cells'.
3. Apply custom time formatting: Navigate to the 'Custom' category, input [m]:ss in the Type field, and click 'OK'.
4. Calculate cost or totals: Use standard formulas like =SUM() or =E2/(1440*D2) in adjacent cells to instantly calculate your time metrics.

100% compatible with Microsoft Excel time formats and formulasFree and lightweight spreadsheet alternativeIntuitive Format Cells interface for easy custom formattingSupports advanced array calculations and time conversions

microsoft office alternative - wps office

Frequently Asked Questions

Why does my cell show 0 or 3 instead of the full minutes and seconds?

This happens if you accidentally use a semicolon instead of a colon in your custom format code (e.g., typing [m];ss instead of [m]:ss). Excel does not recognize the semicolon as a valid time separator.

Why is my total time resetting after 24 hours?

Standard time formats (like hh:mm:ss) represent a time of day and reset after 24 hours. To display cumulative elapsed time beyond 24 hours, you must use square brackets around the hour placeholder, written as [h]:mm:ss.

Why does my cost per minute calculation give a completely wrong number?

Excel stores time as a fraction of a single day. If you divide a cost directly by a time value, you are calculating the cost per day. You must multiply the time value by 1440 (the total number of minutes in a day) to convert it to actual minutes before dividing.

Excel & Data Problems