Calculation Issues

How to Enter Excel Times as Minutes and Seconds

Huda Qurayshi Sep 28, 2026 870 views

Question details

The user needs a way to enter and display time data strictly as minutes and seconds without the spreadsheet application automatically converting the input into hours and minutes.

How to Enter Times as Minutes and Seconds in Excel

Product: Excel
Device & OS: not provided
Scenario: Inputting durations for walking, fitness records, or other timed activities where the value is measured in minutes and seconds.
Observed behavior: Typing a standard time format like '21:47' causes Excel to interpret the entry as 21 hours and 47 minutes instead of 21 minutes and 47 seconds.

Before you start

Determine whether your system locale uses a period or a comma as the decimal separator, as this will affect the syntax required for rapid time entry.

Solution 1Recommended

Use Zero-Hour Input and Custom Formatting

The most reliable method to force Excel to recognize minutes and seconds is to explicitly type a zero for the hours and apply a custom time format.

Cell formatting alone does not change how Excel interprets an initial keystroke entry. Excel's default behavior is to read any two numbers separated by a colon as hours and minutes. You must change the input method alongside the cell formatting to achieve accurate calculations.

Select and format the target cells

Highlight the cells where you plan to enter the time data. Right-click the selection and choose 'Format Cells', or press Ctrl+1 on your keyboard.

Apply the [m]:ss custom format

Navigate to the 'Number' tab and select 'Custom' from the category list. In the 'Type' input box, enter '[m]:ss' to instruct Excel to display total elapsed minutes and seconds. Click 'OK'.

Enter the time with a leading zero

When typing your data, include a zero for the hour. For example, to record 21 minutes and 47 seconds, type '0:21:47' and press Enter.

Use Zero-Hour Input and Custom Formatting

Quick Entry Shortcut: Instead of typing '0:21:47', you can append a decimal zero to your entry. Type '21:47.0' (for locales using a period) or '21:47,0' (for locales using a comma). Excel will automatically recognize this as minutes, seconds, and milliseconds.

Seamless Spreadsheet Solution

Easily Manage Time Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data formatting tools that make managing custom time entries, like fitness or sports records, incredibly easy and accurate. It perfectly supports advanced custom formats and is highly compatible with your existing files.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your fitness or time-tracking records.
2. Open Format Cells: Select the cells you wish to modify, right-click, and select 'Format Cells' (or press Ctrl+1).
3. Set the custom format: Click on 'Custom' under the Number tab, type '[m]:ss' in the Type field, and click OK.
4. Input the data: Type your time entries using the '0:mm:ss' format (e.g., 0:21:47) to properly log minutes and seconds.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and custom number formats.Supports advanced time calculations and custom [m]:ss formatting without data loss.Lightweight, fast, and completely free to use for both basic and advanced data tasks.

microsoft office alternative - wps office

Frequently Asked Questions

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

By default, when Excel sees two numbers separated by a colon (such as 21:47), it assumes the first number represents hours and the second represents minutes. To bypass this, you must explicitly add zero hours (0:21:47) or use a decimal entry.

What does the bracket [m] mean in the custom format?

The bracketed '[m]' tells the spreadsheet to display the total elapsed minutes rather than resetting the minute count to zero once it reaches 60. This is essential for tracking long durations, like a 90-minute workout, which would otherwise display as 1 hour and 30 minutes.

Can I record milliseconds along with minutes and seconds?

Yes. To display milliseconds, you should format your cell using '[m]:ss.000' (or '[m]:ss,000' depending on your regional settings). When entering the data, include the milliseconds at the end, such as 0:21:47.150.

Excel & Data Problems