Convert Seconds to Time in Excel
As times in Excel are simply numeric values, a specified number of seconds can be converted into a time value using simple arithmetic.
As one day in Excel is represented by the decimal value 1.0, one second is represented by 1.0 divided by the number of seconds in a day.
1 second (in Excel) = 1 / 86400
And any number of seconds can be converted into an Excel time by the formula:
Excel Time = Number of Seconds / 86400
Examples are shown below.
Examples of how to Convert Seconds to Time in Excel
|2||145||=A2 / 86400|
|3||5460||=A3 / 86400|
|4||84532||=A4 / 86400|
|5||8||=A5 / 86400|
Results (before formatting):
Results (after formatting):
Note that, in order to display the values in column B of above spreadsheet as times, the cells must be formatted as times (see below).
Formatting Cells to Display Times in Excel
If the result of your time calculation is displayed as a decimal, you need to set the cell's formatting to an Excel time format. This can be done using one of the following methods:
Method 1 - Formatting Through the Ribbon Options
The easiest way to change cell formatting to a simple 'Time' format is to select the cell(s) to be formatted and then select the Time option from the drop-down menu in the ribbon. This is found in the 'Number' group on the Home tab (see below):
Method 2 - Formatting Through the 'Format Cells' Dialog Box
This method can be used in all versions of Excel, including earlier versions that do not have the ribbon:
Formatting Cells to Display More Than 24 Hours
It is likely that the standard time format (applied when you use the ribbon option to format a time) will only display times up to 24 hours.
However, If you use the 'Format Cells' dialog box (see above), you should be able to find a time format that displays more than 24 hours. Alternatively, you can define your own custom time format.
To do this:
See the page on Excel date and time formatting for more details on formatting times in Excel.