4 Replies Latest reply on Jun 28, 2013 7:32 AM by David Andrade

convert integer to mm:ss

Is there a way to convert an integer that is already in seconds to mm:ss?

For example, I have two integers I have divided [sum(x)/sum(y)] to get a total talk time in seconds of 188.90780

If I wanted to get the minutes, I'd divide by 60, so... 188.9078 / 60 = 3.14846

The decimal basically translates to .14846 * 60s = 8.9076 ~ 9 seconds

My answer should bring 3.9 or 3 minutes 9 seconds.

I figured out a way to do this, but it is a really, really long calc that works best as a string and I'm sure someone has a better idea on here.

str(float(left(str((sum([x])/sum([y]))/60),1)))

+"m, "+

str(

if float(left(str((float(str((sum([x])/sum([y]))/60))-float(left(str((sum([x])/sum([y]))/60),1)))*60),2))>10

then

left(str((float(str((sum([x])/sum([y]))/60))-float(left(str((sum([x])/sum([y]))/60),1)))*60),2)

else

left(str((float(str((sum([x])/sum([y]))/60))-float(left(str((sum([x])/sum([y]))/60),1)))*60),1)

end)

+ "s"

See what I mean?

• 1. Re: convert integer to mm:ss

str(int([s]/60))+" minutes "+str((([s]/60-int([s]/60))*60))+" seconds"

Should be a good start

• 2. Re: convert integer to mm:ss

You could also just do:

[s]/(60*60*24)

and set a custom format:

1 of 1 people found this helpful
• 3. Re: convert integer to mm:ss

If you want padding 0 for single digits, then this might be an improvement:

```IIF(LEN(STR(([Seconds]-[Seconds]%60)/60))==1,'0','') + STR(([Seconds]-[Seconds]%60)/60)
+ ':'
+ IIF(LEN(STR(ROUND([Seconds]%60,0)))==1,'0','') + STR(ROUND([Seconds]%60,0))
```

If padding is not important, then it is even simpler:

```STR(([Seconds]-[Seconds]%60)/60) + ':' + STR(ROUND([Seconds]%60,0))
```
1 of 1 people found this helpful
• 4. Re: convert integer to mm:ss

Thanks Alex and Dimitri, these are both very helpful!

What I'd like to do is post the calc in a table that has a bunch of measures listed in it. So, my dimension will be Date and I'll have a bunch of measures in text to view Date by.

One of the problems I'm running into here is that Tableau doesn't easily let me mix a dimension (the m:s calc) with the other measures so I can stack it neatly within the table. Any suggestions as to how I can do this on one sheet?