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

    Convert seconds to mm:ss.ms

    Kris Hallis



      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,


      MM:SS format

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

          First create a calculated field that does the following:



          [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"


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


            • 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))





              Time String Seconds

              // Calculate seconds and format with two places.

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

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





              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"


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



              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"


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


              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!