You can use the following formula in Excel to convert seconds to a hh:mm:ss format:
=TEXT(B2/86400,"hh:mm:ss")
This particular example converts the number of seconds in cell B2 into a hh:mm:ss format.
For example, if the value in cell B2 is 19340 then this formula will display 05:22:20 since 19,340 seconds is equivalent to 5 hours, 22 minutes and 20 seconds.
The following example shows how to use this formula in practice.
Example: Convert Seconds to hh:mm:ss Format
Suppose we have the following dataset in Excel that shows the number of seconds that it took for various athletes to finish some task:
Suppose we would like to display each of the seconds in column B in an hours, minutes and seconds format.
We can type the following formula into cell C2 to do so:
=TEXT(B2/86400,"hh:mm:ss")
We can then drag and fill this formula down to each remaining cell in column C:
Column C now displays the number of seconds in column B in terms of hours, minutes and seconds.
For example:
- 19,430 seconds is equal to 5 hours, 22 minutes and 20 seconds.
- 14,500 seconds is equal to 4 hours, 1 minute and 40 seconds.
- 21,834 seconds is equal to 6 hours, 3 minutes and 54 seconds.
And so on.
How This Formula Works
Recall the formula that we used to convert second to a hh:mm:ss format:
=TEXT(B2/86400,"hh:mm:ss")
Here is how this formula works:
There are 86,400 seconds in one day. Thus, B2/86400 first calculates the number of days in cell B2.
For example, 19340/86400 = 0.223843 days.
Next, the TEXT function displays this number of days in a format that uses hh to represent hours, mm to represent minutes, and ss to represent seconds.
The end result is that 19340 is shown as 05:22:20.
Note that if you wanted to also display the number of days, you could use dd in the TEXT function as follows:
=TEXT(B2/86400,"dd:hh:mm:ss")
For example, this would display 19340 as 00:05:22:20.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Time Duration to Minutes
Excel: Calculate Difference Between Two Times in Minutes
Excel: Calculate Difference Between Two Times in Hours
