Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

formatting time value to hh:mm:ss

I hope I am posting this to the right location as I am developing a new report.  Anyway, this is coming out of our telephone DB system and one column represented is Duration, which is an int column a...
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Anonymous

     

    Try a DAX as below.

    fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

    Or deal with the format in query.

    select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

     

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago

Anonymous

 

Try a DAX as below.

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

Or deal with the format in query.

select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

 

tmilu's avatar
tmilu
Regular Visitor
9 years ago

I'm pretty new to DAX so it's possible that I'm doing something wrong, but your code was calculating the incorrect seconds for me and I made the following changes to get it to work:

 

 

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration]/ 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( (TableName[Duration]- INT (TableName[Duration]/ 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & INT(MOD(MOD (TableName[Duration], 3600),60)), 2 )

 

 

  • bizzybisnette's avatar
    bizzybisnette
    Frequent Visitor
    8 years ago

    Eric_Zhang
    Hello I was able to create the DAX as a calculated column not a measure when creating as a measure I get the following error:

     

    "A single value for column duration in table dialingresults cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

    Either way when I use the code provided:

     

    Time =
    RIGHT ( "0" & INT ( DialingResults[Duration]/ 3600 ), 2 )

        & ":"  

      & RIGHT (     

       "0"         

       & INT ( (DialingResults [Duration]- INT (DialingResults [Duration]/ 3600 ) * 3600 ) / 60 ), 

           2  

      )  

      & ":"  

     & RIGHT ( "0" & INT(MOD(MOD (DialingResults [Duration], 3600),60)), 2 )

     

    It is giving me mis-calculations see attached image.

  • bizzybisnette's avatar
    bizzybisnette
    Frequent Visitor
    8 years ago

    Guys I think I got it.

     

    You need a field that is just seconds and use this code as a measure.

     

    Time = FORMAT(INT(

    IF(MOD([Seconds]|60)=60|0|MOD([Seconds]|60)) +

    IF(MOD(INT([Seconds]/60)|60)=60|0|MOD(INT([Seconds]/60)|60)*100) +

    INT([Seconds]/3600)*10000)| "00:00:00")

     

    Seconds = the field that contains seconds.

  • Mk2018's avatar
    Mk2018
    Frequent Visitor
    8 years ago

    Hi

     

    I am also experiencing this, have you found any solution for this?

     

    Thanks 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks you! I have just updated a little bit your code to use IT in SSAS :

    =FORMAT(INT( IF(MOD(('WEBSITES KPI'[Temps_passé]/1000),60)=60,0,MOD(('WEBSITES KPI'[Temps_passé]/1000),60)) + IF(MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)=60,0,MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)*100) + INT(('WEBSITES KPI'[Temps_passé]/1000)/3600)*10000), "00:00:00")

    It's working fine for me 😄 !

    I have divided by 1000 because I have the duration in milisecondes.