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!
Convert number to time in seconds
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Convert number to time in seconds
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Convert number to time in seconds
Hi Rudi,
Simple: divide by 86400 and format as [hh]:mm:ss
Simple: divide by 86400 and format as [hh]:mm:ss
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Convert number to time in seconds
wow...ok! This works perfectly. Can you explain the 86400? TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Convert number to time in seconds
OK...seconds in a day! I get it :)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Convert number to time in seconds
Thank you for that Paul.macropod wrote:format as [hh]:mm:ss
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.
Regards
Don
Don
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert number to time in seconds
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].
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Convert number to time in seconds
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
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
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: Convert number to time in seconds
Hi Don,Don Wells wrote:Thank you for that Paul.macropod wrote:format as [hh]:mm:ss
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.
You could use a formula like:
=INT(A1/86400)&TEXT(A1/86400," hh:mm:ss")
Do note, though that the result is text.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]