Time with fractional seconds

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

Comments

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!