Convert decimal seconds time in Excel h:mm:ss format

Convert decimal seconds time in Excel h:mm:ss format

Generic formula
=seconds/86400
Explanation

To convert seconds in decimal format to a proper Excel time, divide by 86400. In the example shown, the formula in C6 is:

=B6/86400

To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm.

HoursFractionMinutesSecondsValueTime
11/246036000.041671:00
33/24180108000.1253:00
66/24360216000.256:00
1212/24720432000.512:00
1818/241080648000.7518:00
2424/241440864001.021:00

Since there are 24 hours in a day, 60 minutes in each hour, and 60 seconds in each minute, you need to divide by 24 * 60 * 60 = 86400 in order to convert decimal seconds to a value that Excel will recognize as time.  After dividing by 86400, you can apply a time format of your choice, or use the result in a math operation with other dates or times.

In the example, since B11 contains 43200 (representing 43200 seconds, or a half day) the result is 43200/86400 = 0.5. Once a time format like h:mm or [h]:mm is applied, Excel will display 12:00.

Displaying a time duration

To display hours that represent a duration longer than 24 hours, minutes in durations longer than 60 minutes, or seconds in durations over 60 seconds, you’ll need to adjust the number format by adding square brackets.

h  - for hours greater than 24
m  - for minutes greater than 60
s  - for seconds greater than 60