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

 

Abduvali's avatar
Abduvali
Skilled 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!!!