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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!