8 Replies Latest reply on Nov 12, 2014 12:35 PM by Hrvoje Gabelica

# Minutes and Seconds?

I have a field with average seconds spent on our web site (from Google Analytics)

Easy to build report like

Measure Names
Australia
# Visits889,267
# Visitors204,972
# Page Views15,140,102
Avg. Page Views Per Visit17.03
Avg. Time On Site1,284

Also "easy" to convert time to minutes:seconds

ATTR(STR(INT([Avg. Time On Site (s)]/60))+":"+

IIF(LEN(STR(INT([Avg. Time On Site (s)]) % 60))=1,"0"+STR(INT([Avg. Time On Site (s)]) % 60),STR(INT([Avg. Time On Site (s)]) % 60)))

This will show 21:24 but how do I get it into the table? It becomes an Attribute however I try to (remove the ATTR()) do it. It just gets a dimension...

Or is there other ways to treat time? Formatting?

/P

• ###### 1. Re: Minutes and Seconds?

Te easiest way to do this is with formatting, as you suggested.

What you need to do is turn your time into a fraction of a day, and then format it as hours (if you need that much), minutes and seconds.

To turn it into a fraction of a day, just divide your time in seconds by 86400 (the number of seconds in 24 hours). Then just format it with the string "hh:mm:ss" or just "mm:ss". Like this:

• ###### 2. Re: Minutes and Seconds?

Just want to say thank's to Richard! :-)

/P

• ###### 3. Re: Minutes and Seconds?

Simplified steps for those like me who need a little more help:

1. Create the following calculated field calculation: (sum([Visit Duration])/sum([Visits]))/86400
2. Ensure the measure you created is continuous or the color green. If it's blue or discrete right click on the calculated field measure you created and convert it to continuous.
3. Right click on the calculated field measure you created and click: Default Properties > Number Format...
4. Change the format to: Custom: hh:mm:ss

And you have the magic!

• ###### 4. Re: Minutes and Seconds?

Hi Richard --

I followed these steps to get the time as a fraction of a day (sum([seconds]) / sum([events])) / 86400)

but when I try to display as m:ss (or even mm:ss), it decides that "m" is month instead of minute.  It works fine when I use hh:mm:ss, but not when I leave off the "hh:"  I really need the clean look without the hh.  Can you help, please? TIA!

This won't work:

But this will:

• ###### 5. Re: Minutes and Seconds?

Sorry, I led you astray with that original posting. You need to use "nn:ss" instead of "mm:ss".

I think Tableau (and perhaps Excel) infers minutes when surrounded by hours and seconds, but not when just followed by seconds.

1 of 1 people found this helpful
• ###### 6. Re: Minutes and Seconds?

Richard, you just made my day. Been trying to figure this one out and you just saved me a lot of time.

• ###### 7. Re: Minutes and Seconds?

Don't forget to cast your number of seconds to a Float (ex: with the FLOAT function)  before dividing by 86400, otherwise your time will be rounded.

• ###### 8. Re: Minutes and Seconds?

This works but Tableau resets clock if number seconds is greater than +80000. How can I avoid that?