How to Display Exercise Durations as Minutes and Seconds in Excel
Question details
The user wants to record and display exercise durations in minutes and seconds, but the spreadsheet interprets the inputs as a time of day.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking elapsed time or exercise durations in a spreadsheet.
- Observed behavior
- When entering values like 5:30, Excel interprets it as 5 hours and 30 minutes, often displaying it as an AM/PM time of day instead of 5 minutes and 30 seconds.
Understand that spreadsheet software inherently treats numbers separated by a single colon as hours and minutes. You must adjust both how you type the input and how the cell is formatted.
Enter Complete Time Values and Apply Custom Number Formatting
This solution ensures the spreadsheet accurately logs the duration instead of a time of day, while displaying it cleanly as minutes and seconds.
Formatting changes how a value looks in the cell, but it does not change how the spreadsheet interprets the data. Because Excel assumes a single colon means hours and minutes, entering 5:30 will always be treated as 5 hours and 30 minutes.
To fix this, you must explicitly include a zero for the hour when typing, and apply a custom format to hide the hour from the cell display.
Highlight the specific cells or column where you plan to enter your exercise durations.
Right-click the selected cells and choose "Format Cells" from the context menu, or press the keyboard shortcut Ctrl + 1.
In the Format Cells window, go to the "Number" tab and select "Custom" from the Category list on the left.
In the "Type" text box, enter the format mm:ss (or [mm]:ss if you want durations over 60 minutes to accumulate as minutes rather than rolling over into hours). Click "OK".
When typing your durations into the cells, always lead with zero hours. For example, to record 5 minutes and 30 seconds, type 0:05:30 and press Enter.
Easily Track Exercise Durations Using WPS Office
WPS Spreadsheet offers powerful, intuitive cell formatting options that are fully compatible with Excel. You can accurately track, calculate, and display exercise durations in minutes and seconds without frustration.
- 1. Open WPS Spreadsheet: Launch WPS Office, open your workbook, and highlight the cells you want to format.
- 2. Access Format Cells: Right-click the highlighted area and select "Format Cells", or use the shortcut Ctrl + 1.
- 3. Set Custom Format: Under the Number tab, click "Custom", input [mm]:ss into the Type field, and click "OK".
- 4. Enter the Duration: Type your exercise duration starting with zero hours (e.g., 0:05:30) to log it correctly.
Frequently Asked Questions
Why does my spreadsheet think 5:30 is 5:30 AM?
Spreadsheets are programmed to interpret any two numbers separated by a single colon as hours and minutes, representing a specific time of day. To bypass this default behavior, you must enter hours, minutes, and seconds (like 0:05:30).
How do I track exercise durations longer than 60 minutes?
Use the custom number format [mm]:ss. Placing brackets around the 'mm' forces the spreadsheet to display cumulative minutes, preventing the counter from resetting to zero after 59 minutes.
Why does the formula bar still show AM or PM even after I format the cell?
Custom cell formatting only changes the visual appearance of the cell. The formula bar always displays the underlying raw value using your computer's system time settings. To change the formula bar display, you must adjust your operating system's clock to a 24-hour time format.
