10 Replies Latest reply on Jul 19, 2010 5:07 PM by leftcoastjim

    formula question



      formula question


      I have version 7 of filemaker, and have really just started getting into formulas.  Quite confusing for rookies.  I have a field called TC Duration which lists time code durations.  One entry looks like this 01:00:14:22.  This number represents hours:minutes:seconds:frames.  I have created a new field called MINS/SECS.  I want it to use TC Duration as its reference point, and to drop the first 2 digits and the last 2 digits so it displays 00:14, which represents minutes and seconds.  Can anyone supply the formula that would create this result?  Thanks

        • 1. Re: formula question

          I would suggest creating 2 calculation fields. The 1st would be for the Time. The 2nd would be for Mins:secs. It is just easier that way. "Time_wFrames" is what I named your original field (which is likely Text).

          "z_cTime field", result Time (important)

          Let ( [
          len = Length ( Time_wFrames );
          pos = Position ( Time_wFrames ; ":" ; len ; -1 )
          // start from end, 1st instance of ":" going backwards 
          Left ( Time_wFrames; pos -1 )

          "z_cMinSec", result Text (?)

          Right ( "00" & Minute (z_cTime); 2) & ":" & Right ( "00" & Seconds (z_cTime); 2)

          If you want MinSec to also be time, then there are problems; doable however.

          • 2. Re: formula question

            I believe the Middle function would work. Check it out.


            • 3. Re: formula question

              Again, I'm a rookie.  So I'm not sure what of the above answer I'm supposed to copy into my formula?  Where does the formula start and end?

              • 4. Re: formula question

                Your TCduration field isn't a value time so it must be text as Fenton suspects?  If it is imported, FM might accept it as time but if you enter the field and exit, it will fail validation.  So first thing I would do, also as Fenton suggests, is to turn the data into a true time field.


                Let ( [
                len = Length ( TC Duration );
                pos = Position ( TC Duration ; ":" ; len ; -1 )
                // start from end, 1st instance of ":" going backwards 
                Left ( TC Duration ; pos -1 )


                Copy everything between the dashes (it all is the calculation). Create a new calcualtion field  and name as Fenton suggests (or similarly) then paste this calculation into the calc box.  Be sure that the portion in blue is your field name exactly.  Below, change the pop-up 'calculation result' to TIME.  Keep this field because it will be very important and I suggest you use IT going forward for everything you need to calculate or display which has to do with this time field.

                I don't (necessarily) agree with Fenton to then create a second calculation.  It is very possible that you can simply place this field on your layout and apply LAYOUT LEVEL formatting ... either select the field on your layout and select Format > Time and select 'Format As' and specify mmss (or whatever formatting you wish to display your results.

                NOTE:  If your TCduration field is time (improperly imported) then Fenton's calculation will not work.  Change it to text before applying this technique.  Do you plan on importing this data regularly?

                • 5. Re: formula question

                  Try a calculation field (result type is Time) =

                  "00:" & Middle ( TC Duration ; 4 ; 5 )

                  Format the field to display as mm:ss.


                  P.S. I am not sure what this is good for. If the duration is more than an hour, why would you want to show minutes and seconds only?

                  • 6. Re: formula question

                    this last response was the best and simplest.  The answer as to why I only want to display mins and secs is because these numbers represent scene lengths in video time code, where all scenes start with hour 1 as a default (none are actually longer than an hour), in fact all only run for a few minutes and seconds.  The final 2 digits represent frames, which I do not need to display.  So of the 8 total digits (numbers) I only need to display the middle four.  Thank you all for your responses--lcj

                    • 7. Re: formula question

                      > all scenes start with hour 1 as a default (none are actually longer than an hour)

                      If a scene starts at 01:00:00:00, then 01:00:14:22 is the end timecode, not duration.

                      • 8. Re: formula question

                        You are correct.  However if you subtract the start time code from the end time code you get the duration, which in this case is 00 minutes, 14 seconds, 22 frames.  Like I said, I don't need to display hours or frames.  So dropping the hours and frames leaves me with the desired result.

                        • 9. Re: formula question

                          Not to beat this to death, but if you subtract the start time code from the end time code AND the scene is never longer then an hour, the resulting duration will always have "00" as the hour element and there is no need to discard it. A simple =

                          Left ( Duration ; 8 )

                          would suffice.

                          You can certainly calculate the duration the way you do - my note was only that the referenced field is misnamed.

                          • 10. Re: formula question

                            Got it.  Thanks again!