Calculation Issues

How to Format Excel Cells for Minutes, Seconds, and Hours

Maira Mehtab Sep 22, 2026 871 views

Question details

Users need to correctly enter and format time durations in a spreadsheet so that inputs like '21:47' are recognized as minutes and seconds rather than hours and minutes.

Product: Spreadsheet
Device & OS: not provided
Scenario: Entering elapsed time durations such as race times, video lengths, or working hours for accurate calculation.
Observed behavior: Excel defaults to interpreting two-part time inputs (like 21:47) as hours and minutes, causing incorrect duration values and downstream calculation errors.

Before you start

Ensure you know exactly what duration your data is meant to represent. Note that changing the cell format alone does not alter the underlying data value if the time was initially entered and saved as hours instead of minutes.

Solution 1Recommended

Use the Custom [m]:ss Format for Minutes and Seconds

Apply a custom number format and enter the data using a specific syntax to force the spreadsheet to recognize the values strictly as minutes and seconds.

By default, Excel reads a two-part time entry (like 21:47) as hours and minutes. To fix this, you must enter the time with a leading zero for hours or a trailing decimal for seconds, and then apply a custom format.

Enter the time correctly

To input 21 minutes and 47 seconds, type '0:21:47' (hours:minutes:seconds) or '21:47.0' into the cell so the program calculates the correct base value.

Open Format Cells

Select the cells containing your time data, right-click, and choose 'Format Cells' from the context menu (or press Ctrl+1 on your keyboard).

Apply Custom Format

Navigate to the 'Number' tab, select 'Custom' in the category list, type '[m]:ss' into the Type input box, and click OK.

Understanding elapsed time brackets: The square brackets around the 'm' tell the program to display elapsed time, allowing the minute count to exceed 59 without rolling over into an hour format.

Solution 2

Formatting Existing Hour-Based Values to Total Minutes

Convert time data that was correctly entered with hours, minutes, and seconds into a display of total elapsed minutes.

Format Time Easily in WPS Spreadsheet

WPS Spreadsheet provides robust, highly compatible cell formatting options that mirror Microsoft Excel, making it simple to calculate durations, elapsed times, and detailed schedules effortlessly.

1. Open your file: Launch WPS Spreadsheet and open your existing workbook or create a new one.
2. Input time values: Type your duration using the '0:mm:ss' format to ensure accurate data entry.
3. Format the cells: Right-click the selected cells, choose 'Format Cells', navigate to the 'Custom' category, and input '[m]:ss' to view elapsed minutes and seconds.

Seamless format compatibility with Microsoft Excel (.xlsx) files.Fully supports advanced custom time formats like [m]:ss and [h]:mm.Lightweight and runs smoothly on Windows, Mac, and Linux.Free to use with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my minutes and seconds to hours and minutes?

The default time recognition assumes a two-part entry (e.g., 12:30) represents hours and minutes. To bypass this, you must explicitly include hours as zero (0:12:30) or use a decimal for milliseconds (12:30.0) when entering data.

How do I format time to show hours, minutes, and seconds?

To display hours, minutes, and seconds, use the custom format 'h:mm:ss'. If your total hours might exceed 24 (like adding multiple shifts), use '[h]:mm:ss' to prevent the time from resetting to zero.

Does changing the format fix incorrect time values in the cell?

No. Changing the cell format only alters how the data is displayed. If you typed '21:47' and it was recorded as 21 hours and 47 minutes, changing the format to '[m]:ss' will display a massive number of minutes (1307:00). You must re-enter the data correctly as 0:21:47.

Can I add or subtract times formatted as [m]:ss?

Yes. As long as the underlying data is entered as valid time values, you can use standard addition or subtraction formulas to calculate total durations or differences perfectly.

Excel & Data Problems