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

# Convert seconds to mm:ss.ms

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

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

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

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

Perfect - thanks so much Ken!