Convert Seconds to Time in Excel

How to Convert Seconds to Time in Excel

If you want to convert a number of seconds to a time in Excel, the easiest way is to:

  1. Divide the number of seconds by 86400 (the number of seconds in a day);
  2. Format the result as a time.

    (To format an Excel cell to display a time, select the cell and then select the 'Time' format from the drop-down menu in the Home tab of the Excel ribbon).

Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected
Format a cell as a time by selecting the 'Time' option from the drop-down menu on the ribbon

This method of converting a number of seconds to a time works because dates and times in Excel are stored as simple decimal values, with one day represented by the decimal value 1.0, and one second represented by the decimal value 1/86400 (i.e. 1.0 divided by the number of seconds in a day).

Therefore, any number of seconds can be converted into an Excel time using the formula:

Excel Time = Number of Seconds / 86400

Examples of how to Convert Seconds to Time in Excel

 Formulas:
  A B
1 Seconds Time
2 145 =A2 / 86400
3 5460 =A3 / 86400
4 84532 =A4 / 86400
5 8 =A5 / 86400
 Results (before formatting):
  A B
1 Seconds Time
2 145 0.001678241
3 5460 0.063194444
4 84532 0.97837963
5 8 9.25926E-05
 Results (after formatting):
  A B
1 Seconds Time
2 145 00:02:25
3 5460 01:31:00
4 84532 23:28:52
5 8 00: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.


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

If you require a greater selection of time formats, you may prefer to apply cell formatting using the 'Format Cells' dialog box:

  • Select the cell(s) to be formatted;
  • Open up the 'Format Cells' dialog box by either:

    Dialog Box Launcher in the Number Group of the Excel Ribbon
    • Clicking on the dialog box launcher in the 'Number' grouping, on the Home tab of the Excel ribbon (see rightabove).
    or
    • Using the keyboard shortcut Ctrl + 1   (i.e. press the Ctrl key and while holding this down press 1).
  • Ensure the Number tab of the 'Format Cells' dialog box is selected;
  • From the Category: list, select the option Time.

    (If you want a time other than the default, you can then select one of the options from the list on the right hand side of the 'Format Cells' dialog box).
  • Click OK to apply the formatting and close the dialog box.

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 to format your cell(s), when you select the Time option from the 'Categories:' list, a further 'Type' list will appear on the right hand side of the dialog box (shown below).

Format Cells Dialog Box With 24 Hour Time Option Selected

From within the 'Type' list, select a time format that displays an hour value that is greater than 24, before clicking on the OK button, to close the dialog box.



See also, the page on How to Convert a Time To Decimal.