Convert number to time in seconds

[Rudi]

Hi,

I recieve an import that contains a column representing avg time spent on a web page. I need this number shown as hh:mm:ss. For eg: I have a column with numbers like:

54
69
94
57
130
79
90
123
110

I need 54 to show 00:00:54
and 69 to show as 00:01:09
...etc...

TIA for any help!

[macropod]

Hi Rudi,

Simple: divide by 86400 and format as [hh]:mm:ss

[Rudi]

wow...ok! This works perfectly. Can you explain the 86400? TX

[Rudi]

OK...seconds in a day! I get it :)

[Don Wells]

format as [hh]:mm:ss

Thank you for that Paul.
    Are you aware of a similar technique to inhibit the display of days from rolling into months? I can achieve the desired display using "=TEXT(C1,"00 ")&TEXT(C1,"hh:mm:ss")" as a formula, but then I have lost the numerical attributes of the cell.
    I'm just curious.

[HansV]

As far a I know, the only "cumulative" formats are [h] or [hh], [m] or [mm] and or [ss]. There is no [d] or [dd].

[Don Wells]

Thank you Hans.

[sdckapr]

Don,
If you are working with elapsed time over 31 days, are the hours, minutes and seconds all that important? In these cases the days and decimal fractions to me would seem to be more than enough detail...

Steve

[macropod]

format as [hh]:mm:ss

Thank you for that Paul.
    Are you aware of a similar technique to inhibit the display of days from rolling into months? I can achieve the desired display using "=TEXT(C1,"00 ")&TEXT(C1,"hh:mm:ss")" as a formula, but then I have lost the numerical attributes of the cell.
    I'm just curious.

Hi Don,

You could use a formula like:
=INT(A1/86400)&TEXT(A1/86400," hh:mm:ss")
Do note, though that the result is text.