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