HELP with calculation to convert timecode to min:sec

[nycpost]

Hi there,

I've been working on a calculation to convert timecode into minutes and seconds, but am having some problems getting it to resolve properly. The calculation will round up to the next second if the number of frames is >= 15, roll over to the next minute at 59 seconds, etc. That's all working well, the problem is the calc is inserting zeroes in places where there shouldn't be any and removing them where there should be. Check the screenshots below for some examples:

The format I always want it to return is double digits for both minutes and seconds, like "02:42." The reason why it isn't I think has something to do with the fact the source field on the left (which contains the full timecode number) is a text field and that the calc is set to return a text value. I also think the ORDER in which the Case parameters are listed. For instance, I found that if I order the parameters with seconds starting at < 9 and then going up to 59 that a timecode number like 00:00:59:29 will resolve as 00:60. However, if I order the pararmeters as I have below, with the seconds starting at 59 and going down to zero, that 00:00:59:29 will resolve correctly to "1:00" -- but then it's still missing it's zero before the 1 -- even when I've clearly stated that that case parameter should resolve to "01:00."

Can anyone help me figure this out? I've been banging my head against this for days.

Thanks

Here's the calculation:

Case (

/* if a scene's duration is under 15 frm it should be considered 1 sec long */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ;

"00:01" ;

/* If minutes = 00 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ;

"01:00" ; /* when seconds = 59 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ; /* when seconds = 59 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) > 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ; /* when seconds = 10-58 */ 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) > 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ; /* when seconds = 10-58 */ 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & "0" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ; /* when seconds = 9 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ; /* when seconds = 9 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & "0" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ; /* when seconds = 1-8 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) = 0 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ; /* when seconds = 1-8 */  

/* If minutes < 9 */

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) + 1 & ":" & "00" ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) > 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) > 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 59 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & "0" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) = 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) >= 15 ; 

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & "0" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) + 1 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) < 9 and GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 4 ) < 15 ;

GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 2 ) & ":" & GetValue ( Substitute ( Edited Scene Duration ; ":" ; "¶" ) ; 3 ) ;

)

NOTE: This isn't the complete calculation. I also have parameter groupings for when minutes = 9, are > 9 and < 59, and = 59. I've omitted them from this posting for simplicity's sake. However, if you think it would help, I can post the complete calculation.

[comment]

How about:

GetAsTime ( Left ( Duration ; 8 ) ) + ( Right ( Duration ; 2 ) ≥ 15 ) 

Set the result of the calculation to Time and format it to display as mmss.

Note that the requirement for "double digits for minutes" can be met only as long as Duration is under 100 minutes.

[nycpost]

Wow.

That works brilliantly. I can't believe I spent so much time crafting this bloated Case calculation, when there was something so efficient like this out there. That's amazing. Thank you.

And yes, my Case calc would have only accounted for durations under 100 minutes. While not ideal, and not my original desire, it would have been OK. But since this new calc can handle hours, I think I'll keep that formatted just as it is now. Again, brilliant.

Can I ask, is this GetAsTime calc a well-known, often-used calculation? Did you find it somewhere or write it?

Again, thank you. That was amazing.

[comment]

I've done a few timecode calcs on my own and in response to forum questions (search the forums for "timecode"), but it is a rather rare request - so I don't know that there is anything well-known or established.

At first, I used Div() and Mod() to extract the elements, and Time() to put them back together. Only later it occurred to me that 3/4 of the job is already done...

[nycpost]

Well, thanks again. It really did the trick, and highlighted the importance of efficient code when writing calcs. The complete Case I was working on exceeded the 30,000 character limit at one point. I had a feeling I was going in the wrong direction.