Displaying 150 as 2m:30s

[#1]

Hi all,

I have an expression which evaluates to 150 and I want this displayed as 2m:30s. Dividing the expression by 86400 and then changing the number format to interval mm:ss almost works but the "m" and the "s" don't appear so to the user it's not obvious what this figure represents.

Does anyone know how to achieve this?

Many thanks.

[Miguel_Angel_Baeyens]

Hello,

Use the interval() function so

=Interval(time('03:01') - time('01:00'), 'h:mm:ss')

will return "2:01:00"

Hope that helps.

[#3]

Thanks, but it still doesn't display the "m" and the "s" which is what I need.

[martin59 · Accepted solution]

Hi,

You can try this :

=interval(150/(24*60*60),'m') & 'min ' & interval(mod(150,120)/(24*60*60),'s') & 's'

Hope that helps you

[Miguel_Angel_Baeyens]

Hi,

A possible way -just thought of- is

=Replace(Interval(time('00:07:01') - time('00:01:42'), 'mm:ss'), ':', 'm:') & 's'

EDIT: There's no need to pass on timestamps:

=Replace(Interval(150/84600, 'mm:ss'), ':', 'm:') & 's'

will work too.

[#6]

OK, thanks for the suggestions. I had thought something along those lines would work but hoped there was a simpler solution. It's frustrating that the number cannot be formatted like in Excel to display however you want.

Thanks again.

[#7]

Although this works in a table, it doesn't seem possible to display numbers this way on the dimension axes of a chart. The best I can achieve is 02:30 by formatting the number as an interval. Is that right?

[Miguel_Angel_Baeyens]

Hi,

That's right, since in Number tab in chart properties, format doesn't allow expressions, only format codes, and bot "m" and "s" are format codes.

Regards.

[#9]

Hi all,

This is the solution I used:

time((sum(TalkTime) / sum(CallsAnswered)) /86400, 'm') & 'm: '& time((sum(TalkTime) / sum(CallsAnswered)) /86400, 'ss') & 's')

I've discovered that this will cause an inaccuracy when sum(TalkTime) / sum(CallsAnswered) equates to (for example) 132.6 seconds which should display as 2m:13s but using the above expression will give 2m:12s. As far as I can see, using the above expression will only be out by a maximum of 1 second so it's not a big problem. Having said that, I believe that there can only ever be one correct answer so 1 second out is still inaccurate.

Does anybody know of a way to modify my expression to account for this?

Many thanks.

[nathanfurby]

Try including milliseconds like this:

time((sum(TalkTime)/sum(CallsAnswered))/86400, 'm') & 'm ' & time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'ss') & '.' & time((sum(TalkTime)/sum(CallsAnswered)))/86400, 'f') & 's'

If you want to increase the accuracy even further then change 'f' to 'ff'