Convert time to seconds in excel

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.

 AB
1Time (hh:mm:ss)Seconds (decimal)
202: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.

 AB
1Time (hh:mm:ss)Seconds (decimal)
202: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.
Rate this post