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:
- Divide the number of seconds by 86400 (the number of seconds in a day);
-
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).
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:
|
||||||||||||||||||||||||||||||||||||||
|
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):
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:
- Clicking on the dialog box launcher in the 'Number' grouping, on the Home tab of the Excel ribbon (see rightabove).
- 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).
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.