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?