Sunday, February 26, 2012

yogi_Enter Format And Do Calcs With Time That Includes Fractions Of Seconds

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #445

user ScullerGuy said:
TIme Format Issues
In building spreadsheets the compare times for sports, I need a time format that includes tenths of a second. As in 2:31.5 for 2 minutes, 31.5 seconds. Google Docs does not like this, and converts it to text. It is difficult to perform math functions (like division...) on text fields. How about adding this time format?
Specifically, I want to list times and splits for each rower, and be able to do such things as add the times, average them, divide them, etc. I can do this in an Excel sheet, but as soon as I upload the sheet it rounds the time to the nearest whole second. If I attempt to enter a time such as 2:31.4 into a google docs spreadsheet, it is converted to text. No math allowed with text...
Here is an example of what I am looking to do:
Name 2000 meter Time 500 meter split
John 6:20.4 1:35.1
The 500 meter split is simply the 2000 meter time divided by 4. The issue is that "6:20.4" is viewed as text. If it was in an excel spreadsheet uploaded, the 6:20.4 would be rounded down to 6:20.0.
following is a solution to the problem

here is a picture of the sheet with Custom added in the MenuBar ...