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

     

jbocachica's avatar
jbocachica
Icon for Resolver II rankResolver II
9 years ago
Uhhh... which kind of datasource are you using?
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Straight up SQL Server data table.  Nothing special at all.

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft 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

     

    • Abduvali's avatar
      Abduvali
      Icon for Skilled Sharer rankSkilled Sharer
      9 years ago

      Eric_Zhang

       

      Thank you!!! You are officially a legend =D

       

      Worked really well to convert seconds into time format from a calculation in a measure!!!

       

      To be honest, there is a big lack of time formatting options in Power BI, I would love to see a function that would allow us to display time as a total number of hours like in Excel that would make life so much easier.

       

      but anyway rant is over =D thanks again!!!

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

    • Abduvali's avatar
      Abduvali
      Icon for Skilled Sharer rankSkilled Sharer
      9 years ago

      Eric_Zhang

       

      Hi Eric,

       

      Your code for Power BI solution works well... the only issue Minutes displayed out of 100 min and not as standard 60 min =o?

       

      Any advice?

       

      Thanks

       

      Abduvali

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Had a question about D:HH:MM:SS, hopefully this helps.

         

        Dtime (meas) = 
VAR vDur = <<enter object as seconds>>
    RETURN INT(vDur/86400) & ":" &                       //Days
	RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" &    //Hours 
	RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" &      //Minutes
	RIGHT("0" & INT(MOD(vDur,60)),2)                 //Seconds
  • jbocachica's avatar
    jbocachica
    Icon for Resolver II rankResolver II
    9 years ago
    Well, to use certain functions you must Import your data and not use Direct Query :(