Saturday, October 13, 2012

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:

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,

1 comment:

  1. 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