Google-sheets – Get duration as seconds google spreadsheet

google sheets

I'm calculating some sums of gain over time – therefore I want to divide X by time required to generate X.

image description

The problem here is, that the SECOND function in the =(SUM(B4:B18)/SECOND(SUM(A4:A18)))*60 formula returns second portion (mod 60), not second sum (with hours*3600 and minutes*60).

So how can I convert duration field into integer representing number of seconds for that duration?

Best Answer

If your 0:01:00 is a date/time index (numeric rather than a string) the underlying number is an integer for days and a decimal for time. as there are 24*60*60 seconds in a day the number of seconds represented by 0:01:00 is effectively the number of seconds between midnight and one minute past midnight, which for A1 can be calculated as:

=86400*A1

Solution

Suppose total distance is in column C1 and total time is in D1 then average speed formula would be:

=C1/(int(D1)*24+hour(D1)+minute(D1)/60+second(D1)/3600)

int function is the one I was after.

Google-sheets – Counting minutes/seconds

You should be able to do that quite easy with a summation (do use colons (:) in the notation):

A1=00:13:45
A2=00:05:12

Then use SUM formula:

SUM(A1:A2)

The result will be: 00:18:57

UPDATE 18-01-2013
See example file I´ve created: Google Spreadsheets: Counting minutes/seconds

If you use Neo's formula, to get rid of the points, in combination with an ARRAYFORMULA you will get the result as well:

=ARRAYFORMULA(SUM(TIME(LEFT(B2:B3,2), MID(B2:B3,4,2), RIGHT(B2:B3,2))))

See example file (+1 for Neo)

Best Answer

Related Solutions

Google-sheets – Parsing Time data in Google Spreadsheets

Solution

Google-sheets – Counting minutes/seconds

Related Topic