7 Replies Latest reply on Jul 13, 2017 10:33 PM by Hari Ankem

How to convert seconds into MM:SS Format

Hi All,

I have data in seconds on which i have to do AVG and show in MM:SS format. I used below formula to create a calculated field

1) Formula : Seconds is the column which has data in seconds

IIF([Seconds] % 60 == 60,0,[Seconds] % 60)

+ INT([Seconds]/60) * 100

2) On top of calculated field , i have applied AVG function and custom Format to 00:00 which gives me output as 05:90, but i should supposed to get as 06:30.

• How i converted data into seconds is (EndDate- StartDate)*86400= seconds column

Can someone help me guide, where i am doing wrong calculation or format

Thanks,

Teja

• 1. Re: How to convert seconds into MM:SS Format

Try this:

STR(FLOOR([Seconds]/60))+":"+STR([Seconds]%60)

• 2. Re: How to convert seconds into MM:SS Format

If i use STR function in calculated field, I can't to do average function on top of that right ?

• 3. Re: How to convert seconds into MM:SS Format

Yes, you are right. So, if you need the average, do the average first and then do the formatting. So, you can replace the [Seconds] above with AVG([Seconds]).

• 4. Re: How to convert seconds into MM:SS Format

You can also use the MAKETIME function, but that is suitable if your data includes hours too.

MAKETIME( FLOOR([Seconds]/3600), FLOOR([Seconds]/60), [Seconds]%60)

• 5. Re: How to convert seconds into MM:SS Format

I am receiving value as below but expecting to be mm:ss which should be 05:48

• 7. Re: How to convert seconds into MM:SS Format

You will need to do additional checks to see if the minutes are less than 10, then pad it with a "0".

Or

You can use the MAKETIME option I gave you above. That will keep it simple for you.