**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:

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,

It shows how stupid and arrogant Google is in not providing the right tools. Even Corel Office which was developed 20 years is better than Google Office.

ReplyDelete