Convert Time to Seconds in Excel
Method 1: Simple Multiplication
The first method uses the fact that Excel stores times as decimals, with the number 0 equal to the time 00:00:00, the number 0.25 equal to the time 06:00:00, the number 0.5 equal to the time 12:00:00, etc. Because of this system, a time can be converted to hours, minutes or seconds by multiplying it by the number of hours, minutes or seconds in one day.
| A | B | |
|---|---|---|
| 1 | Time (hh:mm:ss) | Seconds (decimal) |
| 2 | 02:30:30 | =A2 * 86400 |
For the time 02:30:30, in cell A2 of the above spreadsheet, the formula returns the value 9030.
2 hours 30 minutes and 30 seconds is equal to 9030 seconds.
Method 2: Using the Excel Time Functions
The second method uses the Excel Hour, Minute, and Second functions to extract the hours, minutes and seconds from an Excel time.
| A | B | |
|---|---|---|
| 1 | Time (hh:mm:ss) | Seconds (decimal) |
| 2 | 02:30:30 | =HOUR(A2) * 3600 + MINUTE(A2) * 60 + SECOND(A2) |
Formatting the Result
When you convert a time to a decimal, the cell containing the result may have the wrong formatting (e.g. the result may be displayed at a time, instead of a decimal).
In this case, you will need to format the cell to have the Excel ‘General’ format. To do this:
| Right click on the cell(s) to be formatted | |
| Choose Format Cells on the menu bar. (Home, Number) | |
| Select the option General from the list of Categories. |