Time with fractional seconds

ryan.shaver75776

Good morning, I am using Smartsheet to track swim time which has a format of minutes:seconds:fractionalseconds (i.e. 5:00.50).  I would like to see if anyone has a solution for a couple of issues I am seeing:

1. I would like to use the MIN function to look across multiple cells (swim meets) and pull the minimum for the actuals.  The issue here is that S/S does not recognize the time as a number, so it returns a "0" result for the MIN function.

2. Same issue above, not recognizing the time as a number.  I would like to do conditional formatting using the MIN result to compare against time standards for State and National meets.  

Any suggestions?

Ryan

Paul Newcome

Have you tried removing the ":" from the equation? You could use a helper column to do this.

 

=VALUE(SUBSTITUTE([Time Column Name]@row, ":", ""))

 

You could then run

 

=MIN([Helper Column]:[Helper Column])

 

or to display the time without having to rework the ":" back in

 

=INDEX([Time Column Name]:[Time Column Name], MATCH(MIN([Helper Column Name]:[Helper Column Name]), [Helper Column Name]:[Helper Column Name], 0))

 

What this does is pulls the data from the cell in the Time column to match whichever row houses the lowest value in the Helper Column. You could also use the values in the Helper column to run your conditional formatting.