# Q: I have measure in my cube that shows duration on some event in seconds. How can I convert numeric result to string in the format hh:mm:ss?

Q: I have measure in my cube that shows duration on some event in seconds. How can I convert numeric result to string in the format hh:mm:ss?

For example, when my measure value is 635, I would like to see 10:35 (that is 10min and 35 second) as this would be much easier to understand. How can I do this with MDX?

A: Lets say we have a measure [Duration In Sec] that contains duration of some events. We can create new calculated measure [Duration String] that will take value of measure [Duration in Sec] and will translate that value to the string format hh:mm:ss. Here is how we will calculate values for hours, minutes and seconds

- **seconds**: we will divide [Duration in Sec] by 60 and will use a reminder. MDX does not have reminder (mod or %) function, but we can use function *int(<decimal>)* to get integer part of any decimal number. So reminder calculation would be: *x - int(x/60)*60*. As we would like to have leading zero for single digit durations, we will adjust formula: *right('0' + cstr(x - int(x/60)*60), 2)*. Here x = [Duration in Sec]

- **minutes**: we will divide [Duration in Sec] by 60*60 and will use reminder as a resulting number of minutes. Formula for that would be: *Int(x - Int(x/60/60)*60*60) / 60))*. If value of [Duration in Sec] is less than 10min, then we will not need leading zeros to represent minutes. If value of [Duration in Sec] is more than 10min, then we will need leading zeros. So formula for minutes would be:

IIF (x < 60, '',

IIF (x < 600, CStr(Int( (x - Int(x/60/60)*60*60) / 60))

, RIGHT('0' + CStr(Int((x - Int(x/60/60)*60*60) /60)) , 2)

)

)

- **hours**: we will divide [Duration in Sec] by 3600 (60*60) and will use whole integer part. We will not show hours part of the string if duration is less than 1 hour.

So our final MDX formula would be:

CREATE MEMBER CURRENTCUBE.Measures.[Duration String] AS

IIF([Measures].[Duration In Sec] = NULL, NULL,

-- Hours

IIF ([Measures].[Duration In Sec] < 60*60, '',

CStr(Int([Measures].[Duration In Sec]/60/60)) + ':'

)

-- Minutes

+

IIF ([Measures].[Duration In Sec] < 60, '',

IIF ([Measures].[Duration In Sec] < 600,

CStr(Int(

([Measures].[Duration In Sec] - Int([Measures].[Duration In Sec]/60/60)*60*60)

/60))

,

RIGHT('0' + CStr(Int(

([Measures].[Duration In Sec] - Int([Measures].[Duration In Sec]/60/60)*60*60)

/60))

, 2)

)

)

-- Seconds

+ ':'

+ RIGHT('0' + CStr([Measures].[Duration In Sec] - (Int([Measures].[Duration In Sec]/60) * 60)), 2)

)

;

Here are samples of results:

Duration in Sec | Duration String |

7 | :07 |

17 | :17 |

67 | 1:07 |

77 | 1:17 |

607 | 10:07 |

3607 | 1:00:07 |

3677 | 1:01:17 |

Note: It would be also very easy to adjust this formula to disply results like 1h 17min 27sec.