How to Convert Excel Time Values to Total Seconds
Question details
The user wants to convert a recorded time value formatted as hours, minutes, and seconds into a single integer representing the total number of seconds.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Converting elapsed times like 1:23:45 from a stopwatch or timecard into total seconds for further calculation or data logging.
- Observed behavior
- The user needs to transform standard time formats into an absolute seconds value either by altering the display format or by utilizing a formula in a new cell.
Ensure that the time values in your cells are recognized as valid time formats (e.g., 1:23:45) rather than plain text. You can verify this by checking if the data aligns to the right side of the cell by default.
Method 1: Apply a Custom Number Format [s]
If you want to display the existing cell as total seconds without creating an extra column or changing the underlying data, use Excel's custom number formatting.
This method changes how the cell visually displays the time while keeping the actual time value intact, which is useful if you still need to reference the original time in other formulas.
Click on the cell containing the time value you want to convert (for example, cell D2).
Press the shortcut Ctrl + 1 on your keyboard, or right-click the cell and select "Format Cells" from the context menu.
In the Format Cells dialog, click on the "Number" tab, then select "Custom" from the Category list on the left.
In the "Type" input box, erase any existing text and type [s]. Click "OK" to apply the changes.
![Method 1: Apply a Custom Number Format [s]](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-10-19/cb30f35643c5f31463d7eaf106b3b52e.png)
Method 2: Use a Multiplication Formula
If you need the total seconds extracted into a separate cell as a standard number, use a multiplication formula referencing the original time cell.
Effortlessly Calculate Time Values with WPS Spreadsheet
WPS Spreadsheet offers identical formatting and formula functions to Microsoft Excel, making time conversions fast and simple. You can easily manage timecards, track stopwatch times, and convert values across thousands of rows seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your recorded times.
- 2. Access cell formatting: Select your time cells, right-click, and choose "Format Cells".
- 3. Apply custom format: Under the Number tab, pick "Custom" and type [s] to instantly display total seconds.
- 4. Alternatively, calculate with formulas: In an empty adjacent cell, type =86400*A1 to extract the total seconds into a standard numeric format.
Frequently Asked Questions
Why does my multiplication formula result look like 12:00:00 AM instead of a number?
When you reference a cell formatted as a time, Excel often assumes you want the new cell formatted as a time as well. To fix this, simply select the result cell, open the formatting dropdown on the Home tab, and change the format to "General" or "Number".
How do I convert Excel time to total minutes instead of seconds?
You can apply the custom number format [m] to the cell. Alternatively, you can use a formula to multiply the time cell by 1,440 (since there are 1,440 minutes in a day) and format the result as General.
Can I use the HOUR, MINUTE, and SECOND functions to calculate total seconds?
Yes. You can use the formula =HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2). While this works perfectly, multiplying the entire cell by 86,400 is generally much faster and less prone to typing errors.
What happens if my time string is stored as text?
Formulas and custom formats will not work if the time is stored as text. You must first convert it to a valid time. You can use the VALUE function in your formula, such as =86400*VALUE(A2), to force Excel to read the text as a time serial number before multiplying it.
