Google Spreadsheet formula to convert time in mm-ss

Question:


( by CC Coach )

I'm using Chrome on a Mac. 

I have a spreadsheet I'm using for all of my cross country athletes' data. I enter their race times and get their paces calculated. Now I'm trying to crunch some numbers but running into trouble. 

I'm working specifically with mile paces (everything is under 9:00/mile)

1. First of all, I wish I could use a m:ss format easily. But that format is not built-in right now. It's a little maddening, because it's easy to do in Excel and Numbers. I want paces to come out as 6:06. I really don't want 06:06 and definitely not 0:06:00 or 6:06:00. For now, I'm using a time format (15:59 choice) and it works. 

2. My biggest problem is I want to do some calculations and projections based on difficulty of course. I'm trying to take a bunch of paces for lots of runners and do things like add 8 seconds to get a projection for another course. I get a parse error if I do something like d6+:08. It will work if I format all cells as plain text and enter my times like 6.06 and then use my formula as d6+.08. But then I have to retype all the times I copied from my other sheets since they are all in 6:06 format. 

Any help is greatly appreciated.

Solution:

Screenshot of the Spreadsheet:

Format all your cells as Plain Text, in which you have put the values as mm:ss and then put the values. And also format the cells in which you want the results as mm:ss

Format your Cell D6 as Plain Text and then put in the value as mm:ss format.

Now, if your Cell D6 is having value 6:06

and you want to add 8 seconds to it,

then put the following formula in Cell D20:

=IF(MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60)>9;ROUNDDOWN((QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8)/60)&":"&MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60);ROUNDDOWN((QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8)/60)&":0"&MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60))

So now Cell D20 will have the result as 6:14

I have marked the +8 in yellow so that you can easily modify it when you need to add more or less than 8 seconds

Your Cell E6 is having value 5:47

And now if you want to subtract 8 seconds from Cell E6

then put the following formula in Cell E20

=IF(MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60)>9;ROUNDDOWN((QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8)/60)&":"&MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60);ROUNDDOWN((QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8)/60)&":0"&MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60))

So now Cell E20 will have the result as 5:39

And similarly you can manipulate any of the cell and convert it to mm:ss format.


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

Google Spreadsheet formula to Concatenating columns from query results

Question:

( by christopher.r.haley )

I sell items on eBay and log them in google spreadsheet.  I'd like to see which weekday is the best day for me in terms of when I make a sale.  In order to do this I have a column where I log the date a sale was made.  For a short example, assume this is my data (2nd column in the formulate below is necessary because you can't aggregate and non-aggregate on the same column in the SELECT query:

06-18-12

06-19-12

06-19-12

06-21-12

I then have a query to determine which day I sell the most on:

=ArrayFormula(Query((A:B),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 IS NOT NULL GROUP BY dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"))

This gives me the following:

DoW | Occurances

2 | 1

3 | 2

5 | 1

What I want to see is something like this:

DoW (Occurances)

2 (1)

3 (2)

5 (1)

Essentiall, I'd like to concatenate the columns from the query's results. Better yet I'd like the DoW to be the 3 letter representation for the weekday (i.e. 2 = "Mon", 3 = "Tue", 5 = "Thu"). I know I can do all of this with multiple rows/columns and either hide them or use another sheet. However, I would LOVE to do it all in one call, if possible.

Solution:


If you are having the dates in Column A:

6/18/12

6/19/12

6/19/12

6/21/12

Then put the following formula in some other column, like in Column B:

=arrayformula(concat(concat(concat(query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col1");" (");query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Thu (1)

Tue (2)

And if you want the output in Ascending order of days, that is Monday then Tuesday then Wednesday and so on... then try the following formula:

=arrayformula(concat(concat(concat(TEXT(query(ArrayFormula(query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col1"));"select Col1");"DDD");" (");query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Tue (2)

Thu (1)

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

Google Spreadsheet formula to track YouTube views

Question:

( by DigitalSauce )

Hello, Currently I am helping a friend track his YouTube views using a google doc. I want to automate this process by having a script grab this line of code:

<td colspan="2" bgcolor="#cccccc"><font color="green">+4,677</font><br><font color="green">+140,303</font> <br></td>

 

This line of code displays how many views a day he is getting(4,677) per the site: http://socialblade.com/youtube/user/morninafterkill. This line of code also displays the views gotten per month (140,303) How ever I need the script to be able to just grab the socialblade URL from the cell next to it, so in turn I can track multiple channels on the same forum. I would like for it to update its self daily.Can anyone help me with this?

More info: 
Url will be in cell: G*
Views a day in cell: H*
Views a Month in cell: I*
(*any number)

Solution:

Put the following link (URL) in the Cell G2:

http://socialblade.com/youtube/user/morninafterkill

Put the following formula in the cell where you want to get the (average of last 30 days) Views:

=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col1")

Put the following formula in the cell where you want to get the (average) views for month:

=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col2")

And if you wish to get the "Views" and "Views a Month" in adjacent cells then you can get both values using the following single formula:

=SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10))

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

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

Google Spreadsheet formula to calculate frames from a string value

Question:

( by Max Peleh )

Hello, 

I need to convert 00:00:09:16 like number into another format - frames, this number represents a time in Hours:Minutes:Seconds:Frames, for example the result of this specific converted number should be 241 (9 seconds* 25fps(or any other fps value) +16 frames), is there any way to make a formula for this?

It should look something like this 

(x-Hours*90000(frames in 1 hour) + (x-Minutes*1500(frames in minute) + x-Seconds*25(frames in second) + x-frames

Is there any way this could be possible to create in the spreadsheet?

And is there a way to apply this formula to existing document filled with these numbers without recreating the whole document / applying on each cell individually?

Thanks to anyone who will try to help me! =)

Solution:

If you have the following value in Cell A1

00:00:09:16

Then put the following formula in Cell A2:

=QUERY(SPLIT(A1;":");"select ((Col1*90000)+(Col2*1500)+(Col3*25)+(Col4)) label ((Col1*90000)+(Col2*1500)+(Col3*25)+(Col4)) '' ")

The above formula will give you the output as value:

241

And if the value in Cell A1 is: 00:22:14:11

It will display the calculated value as: 33361

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