Solved: formatting time value to hh:mm:ss - Page 2

Anonymous · ‎11-02-2016

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 and represents the call in the number of seconds. I would like to convert and format that to HH:MM:SS. Would something like this work, or is there a better way?

New Duration = FORMAT(TableName[Duration]/60, "HH:mm:ss")

Thanks

Eric_Zhang · ‎11-06-2016

@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

View solution in original post

jbocachica · ‎11-04-2016

Well, to use certain functions you must Import your data and not use Direct Query 😞

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

formatting time value to hh:mm:ss

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024