converting a number into days:hours:minutes

[ToffaJ]

Heres a doozey...

Does anyone know how to convert a straight number into days:hours:minutes. So if you have a number that is say.. 1269859... which are seconds, how can you convert that into days and so on... what is the formula for the cell format???

Thanks in advance

 

[Andrew Poulsom]

Divide by 86400 to get decimal days (60 *60 *24).

To get elapsed hours use the format [h]:mm:ss.

I do not know a format for elapsed days:hours:minutes, but this formula works:

=INT(A1/86400)&" "&TEXT(A1/86400-INT(A1/86400),"hh:mm:ss")

This returns a text string not a value, so you will not be able to use it in other calculations.