4 Replies Latest reply on Jun 26, 2014 2:47 AM by disabled_winfried

    Adding up elapsed time




      I'm trying to let users enter brief time periods quickly in the format of mm.ss (or more often m.ss) - they will always forget the leading 0 in mm.ss.


      I'm having trouble working out converting m.ss to a time field so I can add up the complete elapsed time for different segments. The idea was to split the minutes into their own field, the seconds into a different one, then have a background calculation work it out and convert to hh:mm:ss, which then could be summarised and added up.


      I thought of using LeftValues but will have a variable amount of characters before the period, and LeftWords doesn't consider the period as a break.


      Thanks for any help.

        • 1. Re: Adding up elapsed time

          Try the Replace () function: Replace( text ; start ; numberOfCharacters ; replacementText )


          It can handle variations in strings once given point is found (i.e. the start parameter).  Here is one option:


          Let ( [

            _yourText = your text string ;

            _start = Position ( _yourText ; "." ; 1 ; 1 ) ;

            _noc = Length ( _yourText ) - _start ] ;


          Replace( _yourText ; _start ; _noc ; "" )



          )  // end Let


          You may have to add or subtract 1 from the _start or _noc.  I usually test in the data viewer and adjust as needed.

          • 2. Re: Adding up elapsed time

            You may find it easier using a number field with a companion calculating seconds from the input. You can add up the seconds using a summary field.


            60 * int(time_field) + 100* mod( time_field; 1)


            1.04 => 64



            • 3. Re: Adding up elapsed time

              Thanks Malcolm, that one was almost perfect - I just changed the result to Time and it displays h:mm:ss.

              • 4. Re: Adding up elapsed time

                As long as time_field is a time field (and not text), this calculation is superfluous, since all date and time values are stored as number internally.


                Your original attempt uses a period to separate seconds from minutes.

                I would apply an auto-enter calculation

                     Substitute ( time_field; ["."; ":"]; [","; ":"]; [" "; ":"] )

                which allows for

                     4.9     = 04:09

                     4,9     = 04:09

                     4 9     = 04:09