Convert decimal seconds time in Excel h:mm:ss format
To convert seconds in decimal format to a proper Excel time, divide by 86400. In the example shown, the formula in C6 is:
To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm.
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
For any query or issue, feel free to discuss on http://discuss.eduguru.in