4 Replies Latest reply on Sep 16, 2018 6:10 PM by Kris Hallis

    Convert seconds to mm:ss.ms

    Kris Hallis

      Hi,

       

      I am trying to convert a seconds field (e.g. 12 or 145.62) to mm:ss.ms.

      I have found out how to convert it to mm:ss, but am struggling to add the .ms on the end and ensure correct rounding.

       

      Thanks so much,

      Kris

      MM:SS format

        • 1. Re: Convert seconds to mm:ss.ms
          Ken Flerlage

          First create a calculated field that does the following:

           

          Time

          [Number of Seconds]/86400

           

          Right-click on the field, go to Default Properties then Number Format, then select "Custom" and specify the format as nn:ss.00

          Then you should get what you're looking for.

          See attached workbook.

          • 2. Re: Convert seconds to mm:ss.ms
            Kris Hallis

            Hi Ken,

             

            Thanks very much. Unfortunately, I forgot to mention that I cannot do that. Sorry!

             

            The sheet can switch between showing track results (mm:ss.ms) and field results (distance), which are both stored in the same field ([Result]).

             

            As such, I need to use a calculated field to display the result in the correct format i.e.

             

            if [Result Type] = "Field" then

            str([Result]) + "m"

            else

            ([Result] in mm:ss.ms format) + "s"

            end

            • 3. Re: Convert seconds to mm:ss.ms
              Ken Flerlage

              Ah, I see. I'd suggest creating separate calculated fields for minutes, seconds, and miliseconds to make the logic less confusing:

               

              Time String Minutes

              // Calculate minutes and format with two places.

              IF LEN(STR(INT([Result]/60))) = 1 THEN

                  "0" + STR(INT([Result]/60))

              ELSE

                  STR(INT([Result]/60))

              END

               

              Time String Seconds

              // Calculate seconds and format with two places.

              IF LEN(STR(INT([Result]%60))) = 1 THEN

                  "0" + STR(INT([Result]%60))

              ELSE

                  STR(INT([Result]%60))

              END

               

              Time String Miliseconds

              // Calculate miliseconds and format as 2 decimal string

              // We use REPLACE to remove the "0." that will appear before the string if a decimal exists.

              IF LEN(REPLACE(STR(ROUND([Result]-INT([Result]),2)),"0.", "")) = 1 THEN

                  REPLACE(STR(ROUND([Result]-INT([Result]),2)),"0.", "") + "0"

              ELSE

                  REPLACE(STR(ROUND([Result]-INT([Result]),2)),"0.", "")

              END

               

              Then you can create your final string:

               

              Result String

              // Format final string based on the result type.

              IF [Result Type]="Field" THEN

                  STR([Result]) + " m"

              ELSE

                  [Time String Minutes] + ":" + [Time String Seconds] + "." + [Time String Miliseconds]

              END

              See attached workbook and sample data set.

              1 of 1 people found this helpful
              • 4. Re: Convert seconds to mm:ss.ms
                Kris Hallis

                Perfect - thanks so much Ken!