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.

I.e.

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

 Formulas:
 AB
1SecondsTime
2145=A2 / 86400
35460=A3 / 86400
484532=A4 / 86400
58=A5 / 86400
 Results (before formatting):
 AB
1SecondsTime
21450.001678241
354600.063194444
4845320.97837963
589.25926E-05
 Results (after formatting):
 AB
1SecondsTime
214500:02:25
3546001:31:00
48453223:28:52
5800:00:08

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):

Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected

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:

  • Highlight the cells to be formatted;
    Excel Format Cells Dialong Box With Pre-Set Time Format Selected
  • Open up the 'Format Cells' dialog box.

    (The easiest way to do this is to use the keyboard shortcut CTRL-1 (i.e. hold down the CTRL key and while holding this down, press 1)).
  • Ensure the Number tab of the dialog box is selected;
  • From the Category: list, select the option Time;
  • Select one of the pre-set time formats from the Type: list on the right of the dialog box;
  • Click OK.

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:

  • Highlight the cells to be formatted;
    Excel Format Cells Dialong Box With Custom Time Format Selected
  • Open up the 'Format Cells' dialog box.

    (The easiest way to do this is to use the keyboard shortcut CTRL-1).
  • Ensure the Number tab of the dialog box is selected.
  • From the Category: list, select the option Custom.
  • Enter your own formatting definition (e.g. [hh]:mm:ss) into the Type: input box on the right side of the dialog box.

    (Note that placing square brackets around the hour of a time formatting definition tells Excel to display hour values that are greater than 24).
  • Click OK.

See the page on Excel date and time formatting for more details on formatting times in Excel.