Google Spreadsheet formula to calculate seconds and then convert it in mm-ss format

Question:

( by TheWatchtower )

Hi there. I've been working on a spreadsheet for some time now. It's a list of albums and one of the columns lists the length of the album. I prefer the lengths to be in mm:ss format but Google always rolls anything above 60 minutes to an hour. Is there any way for me to keep it from doing this? Thank you very much.

Solution:

First of all, format the cell(s) as "Plaint Text" and then after formatting input as 11:11 then it will remain as it is: 11:11

To format the cell (or column or row or range), select it and then go to Format >> Number >> Plain Text..

Following is the screenshot of the spreadsheet having values in Column D and formatted as Plain Text:

The above screenshot is showing the values up to row 25, actually values are filled up to row 309, 

So here in my example, I am having values in range D3:D309

Now, first of all lets sum this range and get the result in seconds, so following is the formula which will give result in seconds:

=QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")

The result for the above formula for my example is:

912467

which is in seconds.

The above formula will work if the range is having values only in mm:ss format.

In the above formula I have used range D3:D309, which you can change according to your need. The final output of the formula will be in seconds.

OPTION 1)

mm:ss

Now to get the result in mm:ss format from "total calculated seconds", use the following formula:

=CONCATENATE(ROUNDDOWN(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")/60);":";MOD(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ");60))

The result for the above formula for my example is:

15207:47

which is in mm:ss format.

OPTION 2)

hh:mm:ss

Now to get the result in hh:mm:ss format from "total calculated seconds", use the following formula:

=CONCATENATE(ROUNDDOWN(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")/3600);":";MOD(ROUNDDOWN(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")/60);60);":";MOD(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ");60))

The result for the above formula for my example is:

253:27:47

which is in hh:mm:ss format.

OPTION 3)

dd:hh:mm:ss (dd is for day)

Now to get the result in dd:hh:mm:ss format from "total calculated seconds", use the following formula:

=if(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")>=(24*60*60);concatenate(rounddown(QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")/(24*60*60));":";time(;;QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")));time(;;QUERY(ArrayFormula(if({1,0};INT(LEFT(D3:D309;LEN(D3:D309)-3));if({0,1};INT(RIGHT(D3:D309;2)))));"select ((sum(Col1)*60)+sum(Col2)) label ((sum(Col1)*60)+sum(Col2)) '' ")))

The result for the above formula for my example is:

10:13:27:47

which is in dd:hh:mm:ss format.

NOTE: In each of the above formula, range used is D3:D309, which you must change as per your requirement.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive