I am trying to compile results from a series of timed events and am looking for a way to add and subtract split times that are recorded in minutes:seconds.tenths. Ex: 12:23.6
In Excel I would format these times as mm:ss.0, but I am struggling to figure this out in Panorama.
Any advice?
Ultimately I want to:
Add multiple split times to create a total time for each record.
Use running difference to indicate the time difference between successive records.
Number the results sequentially with an allowance for ties (ex: 1, 2, 3, 4, 4, 6 where the 4th place finishers had the same exact time).
But for starters, I’d be happy to figure out how to format my “times” as a number or time in the mm:ss.0 format.
To do the time arithmetic, you will need the times in seconds. Your time entries would be text, and then you would convert those times to seconds in a number field. The arithmetic would be done in the number field, and then results would be converted to a text format for display. The timepattern( function doesn’t do tenths, so you will have to roll your own. Something like
To number them and determine the ties, you begin by sorting up by time, and then you unpropagate, so that when there are ties the first one remains, but the succeeding matching times become empty.
You then do a FormulaFill in the place field, with seq() going there if the time is not blank, and zeroblank(0) going there if it is. Then you propagate so that the place awarded to the first of the ties is propagated to the others.
Then you go back to the time field and propagate it to restore the times that were made blank.
Hi Dave -
I had a feeling I’d have to head down a “roll my own” road.
I certainly understand the text to number for calculation and then back to text for display. And I think I follow your suggestion that to do this text to number conversion, I’m parsing out each element of the time “mm,” “ss,” and “0” to create a number representing seconds. Makes sense. I’m at a learn-as-I go-level, and it’s been over 2 years since I last worked in Pano. But this seems doable for me.
I did. Though, I think my catch is that the times I’m working with are in the mm:ss.0 format, which doesn’t seem to be supported in Panorama. If I convert those from text to numbers as seconds, as suggested by Dave, then timeinterval( becomes viable.
I will look closer at Time( and see if I missed something.
I can’t remember anyone asking for support for fractional seconds before, but that could be a good idea. I’ve added it to the “maybe someday” list
I was worried about how I’d capture the decimals, but this works. My times are entered as text in a pattern of mm:ss.0, with a colon between “mm” and “ss”, and a decimal point between “ss” and “0” (tenths), so the result of the second array includes whatever follows the decimal point because the decimal point is not a separator. I could keep writing, and strip out the decimal point and what follows from the second array, and then add a 3rd array to grab what follows the decimal point. But as is it appears to be working quite cleanly.
For example, the above statement returns a time of 14:12.9 as 852.90 with “TimeSeconds” field property type set to Number (Float).