Parsing Time data in Google Spreadsheets

Question

I'm keeping cycling diary in a Google spreadsheet.

Each entry has a value of Duration entered as ie. . Then I have the totals where duration is summarized as well. But whenever

Some of the relevant columns:

• Distance
• Duration (entered as 3:45:30 meaning 3 hours, 45 minutes and 30 seconds)

• Average speed - calculated based on the following formula:

  =if(or(isblank(C3);isblank(D3));;C3/(HOUR(D3)+MINUTE(D3)/60+second(D3)/3600))
  

This correctly calculates my average speed.

But. Then I also calculate totals at the top of each column. Distance and time are summed up, but speed is calculated on these two sums because I can't simply make an average of calculated speeds since each of them depends on the course length.

But. Whenever total time exceeds 24 hours, formula for getting hours out of some value returns value between 0 and 23.

So if you run ie. =hour("25:30:20") it will return 1 but I want this (or any other that I'm not aware of) function to return 25. Of course my speed calculation is completely off.

How should I get all hours from my time summary?

Answer 1

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.

Answer 2

A more simple version is =D1/(T1*24) where D1 is distance and T1 is total time.