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.

The following tutorials explain how to perform other common operations in Excel: