Thursday, May 14, 2015

yogi_Rank Times That Include Fractions Of Seconds

                                           Google Spreadsheet   Post  #1956
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    May 14, 2015
post by Thomas Reeve:!category-topic/docs/spreadsheets/OyZoIP6LYiw
Exercise times in sheets
I'm helping a teacher with a spreadsheet that records rock climbing times. He wants to enter something like 3 minutes, 20.4 seconds into a cell and have it display as mm:ss.0. However,  he also wants to rank the times (1st place with fastest time, etc), which means I need this stored as a TIME value. [I have set up a RANK function for him in another cell, but it does not work if the times are text values.]

I have taught him to enter his data as "00:03:20.4" which enters the data as a time value. However, when a time is entered into the cell, the display format always reverts back to "hh:mm:ss" (hiding the tenths of a second). He can "correct" the format by changing the column to the mm:ss.0 format, but this extra step seems to be confusing. 

Is there a way to prevent the cell from reverting the format when I enter a time value?