4 Replies Latest reply on Oct 20, 2013 10:47 PM by c n

convert number to minutes and seconds

Hi, I have a data source which is in seconds. When aggregated this number is quite large (300-1000 seconds is the range). i would like to convert this aggregated number to mm:ss (minutes & seconds).

Going a bit mad trying to work out how to do this, so hoping there is someone out there who can help!

Cheers.

Message was edited by: c n. Range updated

• 1. Re: convert number to minutes and seconds

Hi c n

Create a calculated field using the following formula:

IIF (LEN(STR(INT([number]/60)))=1,"0"+ STR(INT([number]/60)), STR(INT([number]/60))) + ":" +

IIF( LEN(STR(INT(([number]/60-INT([number]/60))*60)))=1, "0"+ STR(INT(([number]/60-INT([number]/60))*60)), STR(INT(([number]/60-INT([number]/60))*60)) )

Find attached a workbook with hypothetical data with the solution.

I hope this help

Best,

Ramon

1 of 1 people found this helpful
• 2. Re: convert number to minutes and seconds

c.n. & Ramon,

An easier way is to compute a time as a fraction of a day and then use custom formatting (hh:mm:ss) to display it as hours, minutes, and seconds.  Richard Leeke describes this technique here:  Re: What are effective ways to display length of time?

The benefits are that the calculation is far simpler and it is also far more efficient (numeric calculations are much better than string manipulation -- of course if you are extracting the data it wouldn't matter as the calculation would just be performed once at extraction.)

Regards,

Joshua

• 3. Re: Re: convert number to minutes and seconds

Hi Joshua,

Thank you so much for the recommendation. Definitively that is a much better and efficient solution.

I edited the workbook adding the Richard Leeke's solution, that you wisely recommeded.

Best,

Ramon

• 4. Re: convert number to minutes and seconds

Thank you both so much for your assistence. I've ended up using the solution by Joshua. The only drawback ive encounted is the display format is hh:mm:ss, and given all my end calculations are less than 20 mins means the 00: for hh: is not required.