How to Enter Minutes and Seconds in Excel Without Losing Duration
Question details
The user needs to enter and display time durations exceeding 60 minutes or 24 hours in Excel as elapsed time rather than a standard time of day.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering logged hours or task times in Excel without the program automatically converting large minute or hour sums into a traditional clock or date format.
- Observed behavior
- Excel stores time and duration as fractions of a day, which causes entered durations to reset their hour or minute counts if they exceed a standard 24-hour day or 60-minute hour limit.
Ensure you select all the cells you intend to use for time data entry before applying the custom format to save time and maintain consistency.
Apply Custom Elapsed Time Formatting
Use bracketed custom formats to prevent Excel from resetting the time after a full day or hour.
By default, Excel formats standard time values based on a 24-hour clock. Placing brackets around the hour or minute placeholder in your custom format overrides this behavior, forcing the cell to display total elapsed time.
Type your duration into the cell using the standard hour-minute-second layout. For example, enter 0:43:14 for 0 hours, 43 minutes, and 14 seconds.
Select the cell or range of cells containing your time data. Right-click the selection and choose 'Format Cells' from the context menu, or press 'Ctrl + 1' on your keyboard.
In the Format Cells dialog, navigate to the 'Number' tab and click on 'Custom' in the Category list. In the 'Type' field, enter [h]:mm:ss to display total hours and minutes, or [m]:ss to display total elapsed minutes and seconds. Click 'OK' to apply.
Easily Manage Time Data in WPS Spreadsheet
WPS Spreadsheet fully supports custom time and duration formatting, allowing you to accurately record elapsed minutes and seconds just like Excel. Whether you are tracking timesheets or race durations, WPS makes calculating elapsed hours completely seamless.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook where you need to track time.
- 2. Select the Data Cells: Highlight the specific cells or columns where duration data will be entered.
- 3. Apply Duration Formatting: Press 'Ctrl + 1' to open Format Cells, select Custom, and enter [m]:ss.
- 4. Input Time and Calculate: Enter your minutes and seconds, and multiply the cell by 1440 if you need to convert it into a standard decimal for payroll or tracking.
Frequently Asked Questions
Why does Excel change my minutes and seconds to a date?
If you enter a number like 45:30 without an hour placeholder, Excel may misinterpret it as 45 hours and 30 minutes, rolling it over into a date value. Always include the hour placeholder, such as 0:45:30, to ensure it registers as minutes and seconds.
What do the brackets [ ] mean in custom time formats?
Brackets tell the spreadsheet to display elapsed time instead of standard clock time. For instance, using [h] allows the hours count to exceed 24 without resetting to zero for a new day.
How do I calculate total elapsed minutes from a time value?
Since spreadsheets store time as a fraction of a 24-hour day, you can multiply the cell containing the duration by 1440 (the total number of minutes in a day) and format the result cell as a General or Number value.
Can I subtract two time values to find the duration?
Yes, you can simply subtract an earlier time from a later time using a basic formula like =B2-A2. Apply the [h]:mm custom format to the result cell to ensure the duration displays accurately, even if it exceeds 24 hours.
