Formatting Issues

How to Stop Excel from Rounding mm:ss Durations

Elise Williams Sep 25, 2026 868 views

Question details

The user needs to prevent Excel from interpreting mm:ss inputs (like 24:25) as hours and minutes, which alters the entered duration and displays incorrect values.

How to Stop Excel from Rounding mm:ss Durations

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering minute and second durations into spreadsheet cells for either visual display or calculation purposes.
Observed behavior: Excel automatically interprets the two-part time entry as hours and minutes, rolling over values that exceed 24 hours and displaying an incorrect result like 25:00 instead of 24 minutes and 25 seconds.

Before you start

Determine whether you need these duration values purely for visual display or if you plan to use them in mathematical calculations later, as this dictates which formatting method to apply.

Solution 1Recommended

Use the [m]:ss Custom Format for Time Calculations

This is the recommended method if you need to add, subtract, or use the durations in formulas.

To ensure Excel understands you are entering minutes and seconds, you must explicitly format the cells before data entry and include a zero for the hours place when typing.

Select the target cells

Highlight the cells or columns where you intend to input your minute and second durations.

Open Format Cells

Right-click the selected area and choose 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.

Apply custom formatting

Navigate to the 'Number' tab, select 'Custom' from the Category list on the left, and type '[m]:ss' in the Type input box. Click 'OK' to apply.

Enter the time data

Input your duration by starting with a zero for the hours. For example, to enter 24 minutes and 25 seconds, type '0:24:25' and press Enter.

Use the [m]:ss Custom Format for Time Calculations

Formatting Success: By entering a zero for hours and using the [m]:ss format, Excel will accurately store and display the time as minutes and seconds, allowing for precise mathematical operations.

Solution 2

Format Cells as Text for Display Only

Use this method if you simply want to display the exact text characters on the screen without performing any time calculations.

Manage Time Data Efficiently

Accurately Format and Calculate Durations with WPS Spreadsheet

WPS Spreadsheet handles custom time formatting identically to Excel, ensuring your minutes and seconds stay exactly how you typed them. It provides a familiar interface, seamless data entry, and comprehensive custom formatting tools.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook. Highlight the cells for your time data.
2. Access cell formatting: Right-click the selected cells and choose 'Format Cells' from the drop-down menu.
3. Set the custom format: Under the 'Number' tab, click 'Custom' and input '[m]:ss' into the formatting box, then click OK.
4. Input your durations: Type your data starting with a zero for the hours (e.g., '0:24:25') to perfectly track minutes and seconds without rounding errors.

Apply advanced custom formats like [m]:ss instantlyFully compatible with Microsoft Excel (.xlsx) file formatsLightweight, fast, and completely free to useFamiliar user interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel convert my minutes and seconds into hours?

By default, when you enter two numbers separated by a colon (like 24:25), Excel's time formatting engine assumes the first number represents hours and the second represents minutes. This causes it to misinterpret inputs meant to be minute/second durations.

Why does my 24:25 display as 25:00 in Excel?

When Excel interprets '24:25' as 24 hours and 25 minutes, it hits the 24-hour rollover threshold. Depending on the exact cell format applied, this rollover can strip the days and only display the remaining minutes or hours segment, which appears as a strange rounding issue showing 25:00.

Can I change the format to Text after entering the time?

No, you must format the cell as Text before entering the data. If you apply the Text format after Excel has already converted your input into a time value, the cell will simply display the underlying decimal serial number (such as 1.01736) instead of your original entry.

Document & File Issues